sqlschemaroledatabase-permissions

SQL Server 2008 Multi-database Permissions via role or schema or scripting


Let's say that there are multiple users in my office that need access to multiple databases that are dependent upon each other. I have users who are sysadmins but as more employees are added I want to be able to apply specific permissions for these new users who are not sysadmins but need owner level permissions on the specific databases without giving them permissions to modify other users. Instead of just adding each one manually I would like to either create a role that can be applied to the user and will give permissions on every database or create a schema that would give these permissions to the user on every database or create a script to give permissions to a user for every database. for example

DB1
 -sysadmin
     (contains user1, user2)
 -role1
     (contains user3, user4)
DB2
 -sysadmin
     (contains user1, user2)
 -role1
     (contains user3, user4)
DB3
 -sysadmin
     (contains user1, user2)
 -role1
     (contains user3, user4)

I want to add user3 and user4 to role1 one time and they will have permissions on all three databases (DB1, DB2, DB3). From my understanding this is basically a server level role, but from what I have read you are not allowed to create custom server level roles.

Also as I understand it schemas are database level specific instead of server level specific (I can't group 3 databases into one schema and then add users to that schema).

So does that leave me with scripting only or am I missing a way to do this with a role or schema?


Solution

  • DECLARE @username varchar(50) = 'mynewuser'
    DECLARE @dbRole varchar(50)
    DECLARE @SQL varchar(max)
    DECLARE @DatabaseName varchar(MAX)
    
    DECLARE my_cursor CURSOR FOR
        SELECT CAST([Name] AS varchar(MAX)) AS databasename
        FROM sys.sysdatabases
        --only user databases
        WHERE DBID>4 AND [NAME] NOT LIKE '$'
    OPEN my_cursor
    
    WHILE 1=1
    BEGIN
        FETCH NEXT FROM my_cursor INTO @DatabaseName
        IF @@FETCH_STATUS <> 0 BEGIN BREAK END
    
        SET @SQL = '
            USE ' + @DatabaseName + ';
            IF NOT EXISTS(
                SELECT p.name 
                FROM ' + @DatabaseName + '.sys.database_principals p
                WHERE p.name = ''' + @username + '''
            )
            BEGIN
                USE ' + @DatabaseName + ';
                CREATE USER ' + @username + ' FOR LOGIN ' + @username + '
                EXEC sp_addrolemember ''db_owner'', ''' + @username + '''
            END'
    
        EXECUTE(@SQL)
    END
    
    CLOSE my_cursor
    DEALLOCATE my_cursor