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

Asger Blond ab-mi at post3.tele.dk
Sat Jan 22 18:06:56 CST 2011


John,
To use Active Directory your server needs to be set up as a Domain Controller. In that case your procedure is correct and should work.
Since it doesn't work I would suppose your server isn't set up as a Domain Controller. I'm not a system administrator guy and can't help on this topic. But maybe others on the list can?
What's puzzling me however is that you are in fact able to access your DiscoSvr\SQLExpress from the workstation. This indicates that your workstation login is validated successfully against the SQL Server on the server DiscoSvr. Something spooky going on here...

Asger

-----Oprindelig meddelelse-----
Fra: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af jwcolby
Sendt: 22. januar 2011 17:16
Til: Sqlserver-Dba; VBA
Emne: [dba-SQLServer] How do I troubleshoot SQL Server Security

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
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com





More information about the dba-SQLServer mailing list