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