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

They have a domain controller somewhere.  This server is not it.

 From what  can tell I was banging against the DiscoSvr's user / group validation rather than the 
domain controller's group / user validation.



John W. Colby
www.ColbyConsulting.com

On 1/22/2011 7:06 PM, Asger Blond wrote:
> 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.
>