Introduction

A common task for DBAs is to grant or revoke security for a person, group or SQL login. It’s usually not difficult but can be tedious. For the users and developers, it’s also tedious because it prevents them from doing their jobs while they wait for security to be changed.

When I interviewed at my company, I asked what a common pain point was for my customers: the developers and managers. The first answer was security. Work often ground to a halt if permissions were missing or incorrect. There was also a great concern that some active directory groups had too many permissions.

General Approach

It wasn’t uncommon for a DBA to have a template set of scripts where they’d plug in the database, the active directory group or SQL login and the specific permission or role. It was easy and pretty repetitive.

Databases that were regularly restored from other environments were frequently put into a SQL job and then a step after the restore would change the rights.

I’ve always tried to automate repetitive tasks and come up with generalized solutions. Since I was always running variations on the same template, I thought I could generalize an appropriate solution.

Below is my solution to the problem because it:

• Centralized security and kept it from being changed
• Allowed the users to run their own security fixes by returning security to a baseline standard, because the stored procedures were rolled up into a SQL job that they could run at will.
• Gave visibility to the developers about what permissions were defined

Types of Security

The lines below came from my generic template. With few exceptions, I was able to create generic roles for security groups. There was either dboUser (to select and update data and run stored procedures) or dboDeveloper (dboUser plus ability to create and modify objects). All security that I ever set followed one of the outlines below:

To grant a Login rights to a Role in a database:

exec sp_addrolemember MyRoleName , MyLoginName

To grant a Login rights to a Role in a database schema:

grant  MyRoleName  on schema ::  MySchemaName  to MyLoginName

To grant a Login rights to a specific object (like a table or procedure) in a database:

grant  MyRoleName  on  MyObjectName  to MyLoginName

To grant a Login rights to a specific permission, such as db_datareader or db_owner in a database:

EXEC sp_addrolemember MyPermission , MyLoginName

To grant a Login access to the instance:

GRANT CONNECT SQL TO MyLoginName;

To grant a Login server level rights to the instance, such as SystemAdministrator:

for SQL 2008

EXEC master..sp_addsrvrolemember @Loginame = MyLoginName , @Rolename =  MyRoleName

for SQL 2012

ALTER SERVER ROLE MyRoleName ADD MEMBER [LoginName]

Data Driven Security

The common values in the above “templates” are:

DBName

LoginName

RoleName

SchemaName

ObjectName

Which become the columns in our Permissions table. Added to that are the following to make it more flexible:

      InstanceName

      PermissionType

In its final form the table looks like this

create table dbo.PermissionDef (
       ID int identity(1,1),
       InstanceName varchar(50),
       DBName varchar(100),
       LoginName varchar(100),
       PermissionType varchar(15),
       RoleName varchar(50),
       SchemaName varchar(20),
       ObjectName varchar(100)
       )

Based on the templates described earlier, you can insert into the table to for the following rights:

insert into PermissionDef (
InstanceName,
DBName,
LoginName,
PermissionType,
RoleName)
select
MyVmDev',
'MSDB',
'Domain\ActiveDirectoryGroup',
'Role',
'SQLAgentOperatorRole'

exec sp_addrolemember N'RoleName', 'LoginName'

insert into PermissionDef (
InstanceName,
DBName,
LoginName,
PermissionType,
RoleName,
SchemaName)
select
'MyVmDev',
'DatabaseName',
'SqlLoginName',
'Grant',
'select',
'dbo'

grant  MyRoleName  on schema ::  MySchemaName  to MyLoginName

insert into PermissionDef (
InstanceName,
DBName,
LoginName,
PermissionType,
RoleName,
ObjectName)
select
'MyVmDev' as InstanceName,
'DatabaseName' AS DBName, 
'Domain\ActDirGroup' as LoginName,
'GRANT' AS PermissionType,
'SELECT' AS RoleName,
'dbo.TableName' as ObjectName

grant  MyRoleName  on  MyObjectName  to MyLoginName

insert into PermissionDef (
InstanceName,
DBName,
LoginName,
PermissionType,
RoleName)
select
'MyVmDev' as InstanceName,
'DatabaseName' AS DBName, 
'Domain\ActDirGroup' as LoginName,
'Member' as PermissionType,
'db_owner' as RoleName

EXEC sp_addrolemember MyPermission , MyLoginName

insert into PermissionDef (
InstanceName,
DBName,
LoginName,
PermissionType,
RoleName)
select
'MyVmDev',
'master',
'SqlLoginName',
'Server',
'Connect'

GRANT CONNECT SQL TO MyLoginName

insert into PermissionDef (
InstanceName,
DBName,
LoginName,
PermissionType,
RoleName)
select
'MyVmDev',
'master',
'Domain\ActiveDirectoryGroup',
'Server',
'sysadmin'

EXEC master..sp_addsrvrolemember @loginame = N'MyLoginName', @rolename = N'sysadmin'

It may seem somewhat complex at first, but it’s able to handle all the security situations that we’ve encountered.

As you move from one environment to another you can copy the security settings as a template and then tweak it after.

insert into PermissionDef (
InstanceName, DBName, LoginName, PermissionType, RoleName, SchemaName, ObjectName)
select 
'MyVmQa' as InstanceName, 
DBName, 
LooginName, 
PermissionType, 
RoleName, 
SchemaName, 
ObjectName
from   PermissionDef
where  InstanceName = 'MyVmDev'

Your developers would be able to access the table with the query below:

select *
from   [dba_instance].Admin.api.PermissionDDef
where  charindex(InstanceName,@@servername) > 0
order by LoginName, DBName

How PermissionDef, the Security Data Table is Used

Once the data is populated, you have to take care of a few things to get the login some rights on that database:

1. Make sure that user exists on that instance, in procedure BaseLogin
2. Make sure that user exists on that database, in procedure ServiceRole

BaseLogin procedure

Step (1)

I addressed by creating a new procedure called BaseLogin. It creates the user on the instance level if that login exists anywhere in the PermissionDef associated with that instance.

In my case, I didn’t deal with SQL logins very often, so the code is specific to our domain. If SQL Logins needed to be created, I did this manually because I didn’t want passwords in my stored procedures or tables and one of its databases exist.

A few structures have been built in. My security table is on another instance (DBA_INSTANCE) and is in a schema called api. My permission settings in the data table grants everyone access to SELECT from this table only. The benefit is that everyone in our company could see what their security settings were without having to ask. api.PermissionDef is a view to the data table. Each instance has a linked server that connects to my DBA_INSTANCE. It’s defined to use a security login that has access to only that api  schema on that Admin.

-- BaseLogin.sql
Use Admin
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BaseLogin]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[BaseLogin]
GO

use Admin
go
CREATE PROCEDURE dbo.BaseLogin
AS
/*
create windows accounts on an instance based on [PROD-VM-DBA].Admin.api.PermissionDef table
NOTE: does not remove any logins. That would be done in Admin.dbo.ServiceRole
exec Admin.dbo.BaseLogin

1/28/14 Andre Quitta Created
6/9/14 Andre Quitta Only add instance level login if a database exists that it's associated to 
and its not an SERVER or CONNECT login, via @ConnectionCount
*/begin
declare @SQL nvarchar(max)
declare @LoginName varchar(100)
declare @ConnectionCount int
set nocount on
IF OBJECT_ID('tempdb..#PermissionDef') IS NOT NULL
DROP TABLE #PermissionDef

-- from table of permissions
select distinct 
LoginName,
cast('' as varchar(100)) as PrincipalsName,
cast(0 as bit) as Done
into #PermissionDef
from [prod-vm-dba].admin.api.PermissionDef
where charindex(InstanceName,@@servername) > 0

-- match on what is already there
delete
from #PermissionDef 
where LoginName in (
select name
from master.sys.server_principals p
)

if (select count(*) from #PermissionDef) = 0
begin
print 'All Base Logins are current'
return
end

if (select count(*) from #PermissionDef where LoginName not like 'SW\%') > 0
begin
print 'Base Logins that are missing that are SQL Logins'
select * from #PermissionDef where LoginName not like 'SW\%'

delete 
from #PermissionDef 
where LoginName not like 'SW\%'
end

-- create the logins
while (select count(*) from #PermissionDef where Done = 0) > 0
begin
set @ConnectionCount = 0
select @LoginName = min(LoginName)
from #PermissionDef
where Done = 0
set @ConnectionCount = 0 
+ (select count(*) from [prod-vm-dba].admin.api.PermissionDef where charindex(InstanceName,@@servername) > 0 and LoginName = @LoginName and DBName in (select name from master.sys.databases) )
+ (select count(*) from [prod-vm-dba].admin.api.PermissionDef where charindex(InstanceName,@@servername) > 0 and LoginName = @LoginName and PermissionType in ('Connect', 'Server') )
--print '@LoginName = '+ @LoginName + ' , @ConnectionCount = ' + convert(varchar(10), @ConnectionCount)
if @ConnectionCount > 0
begin  
set @SQL = '
CREATE LOGIN ['+ @LoginName +'] from windows
'
print @SQL
exec sp_executeSQL @SQL
end -- if @ConnectionCount > 0
else
print '                   Didnt create login ' + @LoginName + ' because there are no databases on ' + @@Servername + ' that use it'
update #PermissionDef
set Done = 1
where LoginName = @LoginName
end -- while (select count(*) from #PermissionDef where Done = 0) > 0
end

Ensure that User Exists on that Database

For database specific permissions, I used code frequently used with security:

use DBName
if not exists (select * from  sysusers where name = 'LoginName')
     create USER  [LoginName] 

For instance level permissions, the code was this:

use master
if not exists (select * from  sysusers where name = 'LoginName')
     create USER  [LoginName] 

ServiceRole procedure

One thing to note is that it relies on dynamic SQL to run the appropriate commands. I’ve done everything that I could to avoid SQL injection and walled off the table that contains the permissions from edits.

The other benefit for me is that I took advantage of SQL box naming conventions. The developer boxes started out with MyVmDev. Each box had its own prefix and set of permissions. The code made sure they all followed the same security standard. If a specific box had specific permissions, I could call it out by name with the code below:

select ROW_NUMBER() OVER (Order by DBName, LoginName, RoleName) AS RowNumber,
*,
cast(0 as bit) as Done
into   #PermissionDef
from   [dba_instance].admin.api.PermissionDef
where  charindex(InstanceName,@@servername) > 0

The procedure will output the SQL statements that gets executed and if there is anything wrong, then will print out the reasons.

One of the really nice parts is that I can define permissions for databases that don’t exist yet, as long as they are valid windows logins. It will instead output the message below:

DBName [YourDBHere]  does not exist, so Domain\ActiveDirGroup/MyPermissionName cannot be used

create procedure dbo.ServiceRole
as
begin
/*
exec Admin.dbo.ServiceRole
Description
   grant service account permissions in non-Production environments
Used by          SQL job SecurityAutoFix
Dependent on         DBAInstance.Admin.dbo.BaseLogin
Outline
-- check if database exists
-- pull all other rights in that database
-- associated login with role or GRANTs
*/declare @SQL nvarchar(max)
declare @DBName varchar(100)
declare @SchemaName varchar(20)
declare @LoginName varchar(100)
declare @PermissionType varchar(15)
declare @RoleName varchar(50)
declare @ObjectName varchar(100)
declare @MinRowNum int
declare @Version varchar(5)
set nocount on
   exec Admin.dbo.BaseLogin
   -- SQL version, either '10' or '11'
   select @Version = left(convert(varchar(20),SERVERPROPERTY ('ProductVersion')),2)
   select ROW_NUMBER() OVER (Order by DBName, LoginName, RoleName) AS RowNumber,
          *,
          cast(0 as bit) as Done
   into   #PermissionDef
   from   [dba_instance].admin.api.PermissionDef
   where  charindex(InstanceName,@@servername) > 0
   while (select count(*) from #PermissionDef where Done = 0) > 0
   begin
       select @MinRowNum = min(RowNumber)
       from   #PermissionDef
       where  Done = 0
       select @DBName = DBName,
          @SchemaName = SchemaName,
          @LoginName = LoginName,
          @PermissionType = PermissionType,
          @RoleName = RoleName,
          @ObjectName = ObjectName
       from   #PermissionDef
       where  RowNumber = @MinRowNum
--print 'acting on DBName [' + @DBName + ']  , ' + @LoginName + '/' + @RoleName + ' for PermissionType ' + @PermissionType
       if (select count(*) from master.sys.databases where name = @DBName) = 1
       begin
          if @PermissionType = 'Role'
              set @SQL = '
                 use [' + @DBName + ']
                 if (select count(*) from sys.database_principals where name = ''' + @RoleName + ''' and type = ''R'' and owning_principal_id is not null) > 0
                 begin
                     if (select count(*) from  sysusers where name = '''+ @LoginName +''') = 0
                        create user  ['+ @LoginName +'] 
                     exec sp_addrolemember N'''+ @RoleName +''', '''+ @LoginName +'''          
                 end
                 else
                     print ''DBName [' + @DBName + ']    ' + @LoginName + ' / ' + @RoleName + ' cannot be used because of a missing Role or the Login is not on the Instance''
              '
          else if @PermissionType = 'Grant' and coalesce(@ObjectName, '') = ''
              set @SQL = '
                 use [' + @DBName + ']
                 if not exists (select * from  sysusers where name = '''+ @LoginName +''')
                     create USER  ['+ @LoginName +'] 
                 grant '+ @RoleName +' on schema :: '+ @SchemaName +' to ['+ @LoginName +']
              '
          else if @PermissionType = 'Grant' and coalesce(@ObjectName, '') <> ''
              set @SQL = '
                 use [' + @DBName + ']
                 if not exists (select * from  sysusers where name = '''+ @LoginName +''')
                     create USER  ['+ @LoginName +'] 
                 grant '+ @RoleName +' on '+ @ObjectName +' to ['+ @LoginName +']
              '
          else if @PermissionType = 'Member'
              set @SQL = '
                 use [' + @DBName + ']
                 if not exists (select * from  sysusers where name = '''+ @LoginName +''')
                       create USER  ['+ @LoginName +'] 
                 EXEC sp_addrolemember N'''+ @RoleName +''', ['+ @LoginName +']
              '
          else if @PermissionType = 'Server' and @RoleName = 'Connect'
              set @SQL = '
                 use master
                
                 if not exists (select * from  sysusers where name = '''+ @LoginName +''')
                       create USER  ['+ @LoginName +'] 
                 GRANT CONNECT SQL TO ['+ @LoginName +'];
              '
          else if @PermissionType = 'Server' and @Version = '10'                  -- SQL 2008
              set @SQL = '
                 use master
                
                 if not exists (select * from  sysusers where name = '''+ @LoginName +''')
                       create USER  ['+ @LoginName +'] 
                 EXEC master..sp_addsrvrolemember @loginame = N'''+ @LoginName +''', @rolename = N'''+ @RoleName +'''
                 --EXEC sp_addrolemember N'''+ @RoleName +''', ['+ @LoginName +']
              '
          else if @PermissionType = 'Server' and @Version = '11'                  -- SQL 2012
              set @SQL = '
                 use master
                
                 if not exists (select * from  sysusers where name = '''+ @LoginName +''')
                       create USER  ['+ @LoginName +'] 
                 ALTER SERVER ROLE '+ @RoleName + ' ADD MEMBER [' + @LoginName + ']
              '
          else
              print 'NO CRITERIA MATCH ??'
          print @SQL
          exec sp_executeSQL @SQL
       end
       else
          print 'DBName [' + @DBName + ']  does not exist, so ' + @LoginName + '/' + @RoleName + ' cannot be used'
       update #PermissionDef
       set    Done = 1
       where  RowNumber = @MinRowNum
   end -- while (select count(*) from #PermissionDef where Done = 0) > 0
end·        

This process allowed me as the DBA to define permissions even before databases were created so the DBA was never a bottleneck for the developers. They were told that whenever a database is created, they could run the SQL job SecurityAutoFix to adjust the permissions to the standard.