Re: Active Directory groups and SQL Server 2008


I do this all the time to manage my rights. Scripted from my server and names changed:

USE master
CREATE LOGIN [Domain\Server_Database_SpecialRole] FROM WINDOWS
USE Database
CREATE USER [Domain\Server_Database_SpecialRole] FOR LOGIN [Domain\Server_Database_SpecialRole]
EXEC sp_addrolemember N'SpecialRole', N'Domain\Server_Database_SpecialRole'

I don't know if you might be facing some other problem, but that should work fine.


"BrassicaNigra" <brassica_nigra@xxxxxxxxxxxxxxxx> wrote in message news:7617F251-847C-49A8-B68F-EF51017C73C0@xxxxxxxxxxxxxxxx

We are setting up a new Windows 2008 server with SQL Server 2008 and were
wondering if on the SQL Server side, access can be granted to an Active
Directory group and then anyone who belongs to this group will have the group
level access to the database(s) (without having to create an individual login
for them in SQL).

So far I have not been able to get this to work. If I give each individaul
a SQL login everything works fine, but I would like to just make each user a
member of an AD group and manage access that way.

Am I barking up the wrong tree?


Dale Hoffman


Relevant Pages

  • Re: SQL2005: Cannot connect error 11001
    ... The famous Windows Firewall (turned on my Server from which I'm trying to ... Exception Details: System.Data.SqlClient.SqlException: Login failed for user ... Try starting the SQL Server ... if you changed the port ...
  • gdm hangs
    ... gdm will hang 9 of 10 times when logging out. ... with or without the client having been connected to the Server. ... # Timed login, useful for kiosks. ... Must output the chosen host on stdout, ...
  • Re: error logging on SQL 2008
    ... I was, though, hoping for some more recognizable Reason for that error (state 11, " Token-based server access validation failed with an infrastructure error. ... Suggests that state 11 means "Valid login but server access failure", which doesn't really seem to rhyme with your description. ... How can you login to your SQL Server and use that tool if you can't log into your SQL Server? ... at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) ...
  • Re: Clearer version of my earlier question re sp_grantlogin and sp_grantdbaccess
    ... > file while you do changes, so the script file in the end contains the ... >> Server, doesn't have the right to grant DB access to Domain Groups. ... > Tibor Karaszi, SQL Server MVP ... >> site where they let me login as Domain Admin. ...
  • Re: SQL 2005 Express setup
    ... Connect to your SQL Server Instance, ... Double click on the Login that you want to add to the "sysadmin" server role or right click on it and choose properties from the popup menu, ... Login is a member of the "sysadmin" fixed server role now. ...