database security

As DBAs we’re in charge of granting access for our databases. Typically we can handle this using active directory groups and SQL Server Management Studio, but this can turn in to a pretty cumbersome and tedious task as the number of databases we have to manage grows. Currently, it’s not unusual for a user to ask me for read permissions on a server that has 20+ databases.   Since it doesn’t really make sense for me to add that user manually to each database on the server (both for time and the likelihood that it will create an error), I’ve created this script for adding a user to the same roles in every user database across a server.

/*Get Database Cursor*/
declare recscan cursor for
select name
from sys.databases
where name not in ('master','tempdb','model','msdb')

/*declare variables*/
declare @db varchar(100)
declare @sql varchar(4000)
declare @user varchar(100)
declare @roles varchar(max)
declare @role varchar(50)
declare @winuser bit
declare @message varchar(max)

/*string parsing variables*/
declare @strpos int
declare @strlen int
set @strpos = 0

/*Set user name and roles to be assigned, comma separated string.
@winuser defines if user is windows(1) or not(0).*/

set @winuser = 0
set @user = <INSERT USER NAME>/*User name, such as DOMAIN\msfal for windows or report_reader for direct db login*/
set @roles =<Comma list of roles>/*i.e.'db_datareader,db_datawriter'*/

/*create server login*/
Print 'Creating user ['+@user+'] on server...'
if @winuser = 1
begin
	set @sql= 'USE [master] CREATE LOGIN ['+@user+'] FROM WINDOWS WITH DEFAULT_DATABASE=[master]'
end
else
	set @sql= 'USE [master] CREATE LOGIN ['+@user+'] WITH PASSWORD = '''+@user+'!123'''
exec(@sql)

/*Cycle through DBs to assign roles.*/
open recscan
fetch next from recscan into @db
while @@fetch_status = 0
begin
	/*Add user if user does not exist in database.*/
	print 'Adding ['+@user+'] to ['+@db+']...'
	set @sql = 'USE ['+@db+'] CREATE USER ['+@user+'] FOR LOGIN ['+@user+']'
	exec(@sql)

	/*Parse role string and assign each role to the user.*/
	Print @db + char(10) + '****************************'
	while @strpos < LEN(@roles)
	begin
		select @strlen = case when charindex(',',@roles,@strpos) = 0 then LEN(@roles)-@strpos+1 else charindex(',',@roles,@strpos) -@strpos end
		select @role = SUBSTRING(@roles,@strpos,@strlen)

		print char(9)+'Adding '+@user+' to '+@role+' role in '+@db+'...'
		set @sql = 'USE ['+@db+'] EXEC sp_addrolemember N'''+@role+''', N'''+@user+''''
		exec(@sql)
		select @strpos = case when charindex(',',@roles,@strpos) = 0 then LEN(@roles) else charindex(',',@roles,@strpos) + 1 end
	end

print char(10)
fetch next from recscan into @db
set @strpos = 0
end

/*clean up cursor*/
close recscan
deallocate recscan

The script itself may look intimidating, but really it breaks down into 3 steps:

1 – Create the user as a server login
Then, for each database
2 – Create the user within the database
3 – Assign the user to the each role within the passed list of roles.

To use this script, all you need to do is set @user to the name of your user, set @winlogin to 1 or 0 if your user is an Active Directory login or not, then insert a comma separated list of roles you want to add the user to. Note, you’re not restricted to the default roles, you can use custom created roles in the script. Just make sure the role exists.

The only other item of interest here is the code section that parses through the role array. Take a look at it if you’re looking for away to split a string into its component parts using T-SQL.

Feel free to download the script here.