1

I have a lot of SQL 2005 servers with a lot of databases and a lot of Windows users that need access to all the servers but only a few databases, and that last part changes regularly. Managing these separately is messy. What I'd like to do:

  • Add all my relevant windows users to an Active Directory Group called "DB_ACCESS"
  • Add a group login to all my SQL servers for MYDOMAIN\DB_ACCESS
  • Grant individual members of that group permission to individual databases when needed

I tested it in the most obvious configuration (added a user to DB_ACCESS, added DB_ACCESS to the server's list of logins with a default role of public, added the individual user to a database) and the user could login to the server but not access the database.

Is this possible? Am I just missing a key configuration item?

1
  • I too have wished this for a long time. But alas I don't know of a way to do it.
    – Tatas
    Jul 12, 2011 at 15:47

1 Answer 1

0

You've likely considered something like the following:

SET NOCOUNT ON GO

BEGIN DECLARE @lsCmd varchar(255)

DECLARE lcsrCmd CURSOR
FOR

SELECT 'GRANT view definition on dbo.' + so.name + ' TO [MYDOMAIN\UserName]' FROM sysobjects so , sysusers c WHERE so.type IN ('V','U','TF','IF','P') and so.name not like 'dt_%' and so.name not like 'sys%' and so.name not like 'pk_%' and so.name not like 'DF_%' and c.name like 'dbo%' order by so.name

OPEN lcsrCmd
FETCH NEXT FROM lcsrCmd INTO @lsCmd
WHILE (@@fetch_status <> -1) BEGIN
    IF (@@fetch_status <> -2) BEGIN
        PRINT @lsCmd
        BEGIN TRY 
            exec(@lsCmd)
        END TRY
        BEGIN CATCH
            PRINT 'Error:  ' + @lsCmd
        END CATCH
    END
    FETCH NEXT FROM lcsrCmd INTO @lsCmd
END

CLOSE lcsrCmd
DEALLOCATE lcsrCmd

END GO

3
  • Doing the granting/revoking isn't really the problem I'm trying to solve -- I mostly want to keep things as clean as possible.
    – sh-beta
    Jul 16, 2011 at 20:54
  • Not sure of another way. I routinely use AD groups in SQL Server to manage same permissions for large numbers of users. That being said that AD group is what is referenced at the database level for object permissions as that AD Group exists at the SQL Server level and SQL Server really doesn't know about the group members.
    – jl.
    Jul 18, 2011 at 15:33
  • Sounds like what I asked just isn't possible. If you add this to your answer, I'll check it as the solution.
    – sh-beta
    Jul 18, 2011 at 18:31

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .