Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
    
I too have wished this for a long time. But alas I don't know of a way to do it. –  Tatas Jul 12 '11 at 15:47

1 Answer 1

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

share|improve this answer
    
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 '11 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 '11 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 '11 at 18:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.