Set Permissions Across SQL Server Database Instances

As a followup to my post on SQL Server Principals & Roles Permission Audit, it’s completely expected that you may want to act on the results of that audit. Specifically, let’s say you want to organize users into groups and enforce group permissions across the instance. In a particular scenario at my company, we are constantly refreshing databases down from production, which has a nasty side-effect of destroying the finely-tuned permissions we’ve carefully set in place. What made the most sense to reduce the friction this would cause was to reset the permissions via a job. This allows us to schedule it nightly, fixing any broken permissions, but also makes it easy to fire on demand, should needs dictate. Of course, it would be easiest if this were all contained in a stored procedure, but how do we juggle all these users and groups when you’re hosting 50 databases?

What made sense for us was to be able to supply the name of a principal, a “like name” for the database, and the role that they should have. What the “like name” allows us to do is apply a permission to a series of databases based on name. For example: ideaexcursion1, ideaexcursion2, ideaexcursion3 can all be set by supplying the paramater ideaexcursion%. This increases the complexity by forcing us to use a cursor in the proc, but reduces the number of actions we need to manage.

exec dbo.pr_SetGroupPermissions
      @principal = 'domain\principalname'
    , @db_likename 'ideaexcursion%'
    , @addrole 'db_owner'

This snippet will—as you’ll soon see—give domain\principalname the db_owner role in any databases that match LIKE ideaexcursion%. Here’s the magic behind the stored procedure:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

create proc dbo.pr_SetGroupPermissions @principal sysname, @db_likename sysname, @addrole sysname, @printonly bit = 0
as
declare
          @database sysname
        , @cmd nvarchar(max)
 
declare csr_dbname cursor for
select name from sys.databases where state = 0 and name like @db_likename
 
open csr_dbname
 
fetch next from csr_dbname
into @database
 
while @@fetch_status = 0
begin
 
        set @cmd = '
USE '+quotename(@database)+'
 
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'''+@principal+''')
BEGIN
        IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'''+@principal+''')
                CREATE USER '+quotename(@principal)+' FOR LOGIN '+quotename(@principal)+'
        ELSE
                ALTER USER '+quotename(@principal)+' WITH LOGIN = '+quotename(@principal)+'
 
        EXEC dbo.sp_addrolemember N'''+@addrole+''', N'''+@principal+'''
END
'
 
        print @cmd
        if @printonly = 0
                exec(@cmd)
 
        fetch next from csr_dbname
        into @database
 
end
 
close csr_dbname
deallocate csr_dbname

And here’s a few notes on what the code does:

• Line 1: Parameter declaration. Notice that there is an optional @printonly param if you’d like to see the resulting code, but not execute it
• Line 7-8: Set up the cursor. On line 8 is the trick that allows us to use a “like name” to simplify the rules. Also, note that we check for “state = 0″ in the predicate, which corresponds to those with a state of ONLINE. If we didn’t check this, we would potentially attempt to execute commands against databases in RESTORING, RECOVERING, RECOVERY_PENDING, SUSPECT, EMERGENCY, or OFFLINE states
• Line 18-30: The main code which will be evaluated and executed. There are several parts to it to accommodate a variety of situations:
  • Line 19: Switch context to the correct database, since we’re executing within the confides of dynamic SQL. Also, the name is qualified with quotename(), which means that it should not be qualified when being passed in
  • Line 21: Validate that the user or group exists, otherwise we will get an error when trying to set it. Since we’re concerning ourselves with permission to a database, and not an instance, we don’t bother creating a user if it doesn’t exist. This is probably smart from a security perspective
  • Line 23-24: Check if the principal exists within the specific database. If not, create the user
  • Line 26: If the user already exists, fix any mismatched SIDswith ALTER USER. This is the preferred way to fix mismatched a mismatched SIDwhich was previously handled by sp_change_users_login, which has since been deprecated
  • Line 28: Assume database login is not valid and add the user or group to the specified role
• Line 32:  Always print the commands to be executed
• Line 33-34: Execute the code, unless the @printonly parameter has been specified

There you have it. A proc that grants specific roles to a principal across your entire database instance.