[dba-SQLServer] How do I troubleshoot SQL Server Security

jwcolby jwcolby at colbyconsulting.com
Sat Jan 22 10:16:02 CST 2011


What I want to do:

Create an instance of SQL Server running on a specific server, and somehow magically allow a 
specific set of users logged in to the "system" (not necessarily that server) to access the server.

AFAIK Users log in to Active Directory.  I do not use Active Directory on my home / business network 
and do not really understand how it works.  In any event it appears that I am going to have to use 
Active Directory in the SQL Server login?

What I tried to do:

On the server (windows 2000) I:

1) Created a windows security group DISUsers
2) Added most of the normal users of the computer, all of which will use the SQL Server 2005 Express 
database, including my user.
3) In SQL Server 2005 Express SSMS at the server level I clicked security login
4) I created a login DiscoServer and I assigned that to the group DiscoSvr\DiscoUsers
5) I checked that the login had the public server role (and only that role)
6) I went into the DISCO database and clickde SDecurity / Users
7) I created a DiscoUsers user assigned to the DiscoSvr\DiscoUsers object (group)
7) I checked the Role members db_DataReader and db_DataWriter

I saved all of the above.

I then go to my workstation, log in, open ssms and select DiscoSvr\SQLExpress.

I can see the database object and I can see a + symbol but if I try to expand the database to see 
the objects I get a very generic error message "DispecLLC\jcolby is not able to access the database 
"Disco" under the current Security Context: SQL Server Error 916"

I do not understand network security, but AFAICT what is happening is that I am logging in to a 
network (Active Directory) login (which all users do) but I have just set up SQL Server on that 
server to use a group / users specific to that server.

So is it possible to use a similar method to manipulate SQL Server with active directory groups / 
users?  How is that done.

-- 
John W. Colby
www.ColbyConsulting.com


More information about the dba-SQLServer mailing list