Managing SQL Server Login in AD & Default Database Rights

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rahulpt - 28 Jul 2006 08:34 GMT

Dear All

I need to configure appox 50+ servers for monitoring in Enterprise Admin for
group of AD users. I created AD group and added users in group. But when I
try to register server it gives error as "Login Failed". As per my knowledge
we need to assign the AD group to login to SQL Server.But how can we assign
AD group to log on to SQL Server?Can we achieve it by adding AD grooup in SQL
Login in Enterprise Admin? and when we add user to SQL Login it asks for
"Database" name, like master etc or user created like test,test1 etc.

So which database we need to assign while adding user to sql login and what
will be security implications of same? I.e.if we add user with database as
master or user created database like test then what rights the user will
have on these databases??

Signature

Rahul

Reply to this Message

Uri Dimant - 28 Jul 2006 08:45 GMT

> we need to assign the AD group to login to SQL Server.But how can we
> assign
> AD group to log on to SQL Server?

In the EM navigate to Security folder and the click Logind ----New Login.
Choose from the list AD group and add it to the SQL Server

Take a look at
sp_addlogin,sp_grantdbaccess,sp_defaultdb,sp_adduser,sp_addsrvrolemember
stored procedures in the BOL

> Dear All
>
[quoted text clipped - 15 lines]
> master or user created database like test then what rights the user will
> have on these databases??

Reply to this Message

rahulpt - 28 Jul 2006 10:26 GMT

Hi Uri

Thanks for reply. But while adding AD user/group to SQL Login it prompt for
Database name. So whether we should seletc system provided databases like
master,msdb or we can specify User Created databases? and what could be
security implication when we add user to such database.Whether he will have
full access to database or only read permissions? Grateful if you can clarify
on the same? Also when we add user to database he will have access to only
that database or other databases also?

Signature

Rahul

> Hi
> > we need to assign the AD group to login to SQL Server.But how can we
[quoted text clipped - 27 lines]
> > master or user created database like test then what rights the user will
> > have on these databases??

Reply to this Message

A McGuire - 28 Jul 2006 14:34 GMT

By default all logins inherit permissions from the public role, which has
sufficient access to the system databases. You just need to explicitly
grant access to the user databases and assign those database users to a
database role. That will work best for you. Check BOL for database roles
like db_datareader, db_datawriter, etc. You may even create a custom
database role for your users. Don't, by any means, assign them to
db_owner - that is way more than the average user needs.

> Hi Uri
>
[quoted text clipped - 47 lines]
>> > will
>> > have on these databases??

Reply to this Message

rahulpt - 28 Jul 2006 16:06 GMT

Hi McGuire

Thanks for reply but what rights "Public" role will have on System & user
created database. Instead of User datbase can I give System Database like
Master??? & what will be security implications for same. I am looking at
group of operators which will perform just monitoring task and should not
have more access to it. In such cases which database we recoomend? User
Created or System like Master,MSDB etc.??

Signature

Rahul

> By default all logins inherit permissions from the public role, which has
> sufficient access to the system databases. You just need to explicitly
[quoted text clipped - 55 lines]
> >> > will
> >> > have on these databases??

Reply to this Message

A McGuire - 28 Jul 2006 16:38 GMT

Well, if they will be doing administrative tasks, you can search for
information on fixed server roles
(http://vyaskn.tripod.com/sql_server_security_best_practices.htm).

You can check the permission for the public database role by going to any
database (master, for example) and double-clicking on "public", then click
"Permissions". Everyone is a member of public, so that is what they will
have by default. If they need more, you may first toy with
db_securityadmin, db_datareader, etc. to see if they can do what they need
to do. It is somewhat of a mystery to me what explicit permissions are
needed to stored procedures and tables in order to access a server via the
Enterprise Manager or Profiler, for example.

For monitoring, I would have to say db_datareader should be good, but you
should test it out. Trial and error often is the only way to find out
because like I said, I haevn't found a resource that tells me exactly what
is needed to run the administrative tools.

Our company has to REVOKE all access to the public role, so even our
db_securityadmin database role is useless since it inherits most of its
permissions from being a member of public. I have been forced to assign
semi-administrators to the db_owner role until I find a suitable resource
for creating a database role for particular applications such as the
Profiler or Enterprise Manager.

> Hi McGuire
>
[quoted text clipped - 71 lines]
>> >> > will
>> >> > have on these databases??

Reply to this Message