x

logins to sa

hello all, i am new to sql server admin, is it possible to add users to sa login in sql server 2005. please reply, its urgent.
more ▼

asked Dec 23, 2011 at 04:33 AM in Default

sunithalee143 gravatar image

sunithalee143
267 15 23 25

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first
First of all I would like to make the correction, that you do not add users to the logins but you create users for the logins. Moreover, a login can have associated users in different databases, but only one user per database. Since dbo user is already mapped to the "sa" login by default in each database, so you cannot add users to sa login until the default dbo user is there. And AFAIK, you cannot drop it since all members of the sysadmin fixed server role are always mapped to dbo.
more ▼

answered Dec 23, 2011 at 05:08 AM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

(comments are locked)
10|1200 characters needed characters left

"sa" in SQL Server is a login ID, not a user group. You could give out the password for sa so that many people can use it, however, this is security nightmare.

Marginally less nightmarish is to add the users you want to the local admin group on the server hosting SQL Server, and make sure that that group is in the serveradmin role on SQL Server.

A better approach is to set up a user group in Active Directory, and setting that group up as a member of the serveradmin role; then all you have to do is add the appropriate users to that AD group.

Alternatively, you could create one user ID for each person and assign each of those to the serveradmin role. This makes it more obvious to the DBA who has access, but increases the management overhead.

However, what you should definitely be doing is ensuring that only DBAs have serveradmin rights over your SQL Server(s), and assign other rights to users accordingly.

See also:

more ▼

answered Dec 23, 2011 at 05:40 AM

ThomasRushton gravatar image

ThomasRushton ♦
33.9k 18 20 44

(comments are locked)
10|1200 characters needed characters left
more ▼

answered Dec 23, 2011 at 08:13 AM

salum gravatar image

salum
213 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1948
x162
x84

asked: Dec 23, 2011 at 04:33 AM

Seen: 1128 times

Last Updated: Dec 23, 2011 at 05:29 AM