Script to create grants to roles to database for SQL authenticated users.

Code that be used to scripts to extract role permissions for a database for SQL authenticated accounts, it is useful when rebuilding a server and after databases are reattached the SQL authenticated users access has to be granted.

DECLARE db_cursor CURSOR FOR
SELECT 'exec sp_addrolemember ''' + g.name + ''', ''' + sl.name + '''', sl.name
FROM sysusers u
, sysusers g
, sysmembers m
, master.dbo.syslogins sl
WHERE g.name IN (SELECT name FROM dbo.sysusers WHERE ([issqlrole] = 1 OR [isapprole] = 1))
AND g.uid = m.groupuid
AND sl.sid = u.sid
AND g.issqlrole = 1
AND sl.isntuser = 0
AND sl.name != 'sa'
AND u.uid = m.memberuid
ORDER BY sl.name

DECLARE @l_sql_stmt VARCHAR(200)
DECLARE @l_user VARCHAR(200), @l_prev_user VARCHAR(200)

SET @l_prev_user = ''
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @l_sql_stmt, @l_user
WHILE @@FETCH_STATUS = 0
BEGIN
IF @l_prev_user != @l_user
BEGIN
PRINT 'IF EXISTS (SELECT 1 FROM sysusers WHERE [name] = ''' + @l_user + ''')'
PRINT ' EXEC sp_revokedbaccess ''' + @l_user + ''''
PRINT 'EXEC sp_grantdbaccess ''' + @l_user + ''''
SET @l_prev_user = @l_user
END
PRINT @l_sql_stmt
FETCH NEXT FROM db_cursor INTO @l_sql_stmt, @l_user
END
CLOSE db_cursor
DEALLOCATE db_cursor

How to find permissions granted on individual objects in a database?

Run the following SQL to find permissions granted on individual objects on the database

SELECT
o.name AS object_name,
x.name AS user_name,
MAX(case when p.action = 193 then 'X' ELSE '' end) AS 'SELECT',
MAX(case when p.action = 197 then 'X' ELSE '' end) AS 'UPDATE',
MAX(case when p.action = 195 then 'X' ELSE '' end) AS 'INSERT',
MAX(case when p.action = 196 then 'X' ELSE '' end) AS 'DELETE',
MAX(case when p.action = 224 then 'X' ELSE '' end) AS 'EXECUTE'
FROM sysusers u
INNER JOIN sysprotects p ON u.uid = p.uid
INNER JOIN sysobjects o ON o.id = p.id
INNER JOIN (SELECT name FROM sysusers uu) x ON x.name = u.name AND o.name = 'object_name'
GROUP BY o.name, x.name

How to list the access the roles the different users have?

– print usernames and the roles assigned to the user, it can be run against any database and it will print the users and the roles assigned to different users
SELECT sl.name, g.name
FROM sysusers u, sysusers g, sysmembers m, master.dbo.syslogins sl
where g.name IN (SELECT name FROM dbo.sysusers WHERE ([issqlrole] = 1 OR [isapprole] = 1))
and g.uid = m.groupuid
and sl.sid = u.sid
and g.issqlrole = 1
and u.uid = m.memberuid
and sl.denylogin = 0
order by sl.name

Sample output:
localuser db_owner
localuser db_datareader
domain\ADuser db_datareader
domain\ADuser db_denydatawriter
….

How to create SQL scripts to recreate grants on a database granted through a role?

DECLARE @role_name VARCHAR(100)
DECLARE @member_name VARCHAR(100)
DECLARE @rolescur CURSOR
DECLARE @rolememberscur CURSOR

DECLARE @loginname VARCHAR(100)
DECLARE @username VARCHAR(100)
DECLARE @logincur CURSOR

PRINT '– Grant user access'
SET @logincur = CURSOR FOR SELECT l.name, u.name
FROM master..sysxlogins l, sysusers u
WHERE l.sid = u.sid AND l.name <> 'sa'
OPEN @logincur
FETCH NEXT FROM @logincur INTO @loginname, @username
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'EXEC sp_grantdbaccess ''' + @loginname + ''', ''' + @username + ''''
FETCH NEXT FROM @logincur INTO @loginname, @username
END
CLOSE @logincur
DEALLOCATE @logincur

— cursor for roles (user and built-in)
SET @rolescur = CURSOR FOR SELECT name FROM dbo.sysusers WHERE ([issqlrole] = 1 OR [isapprole] = 1) AND name <> 'db_owner'

— loop through roles
OPEN @rolescur
FETCH NEXT FROM @rolescur INTO @role_name
WHILE @@FETCH_STATUS = 0
BEGIN

PRINT '— ' + @role_name
— cursor for members in the roles
SET @rolememberscur = CURSOR FOR SELECT u.name
from sysusers u, sysusers g, sysmembers m
where g.name = @role_name
and g.uid = m.groupuid
and g.issqlrole = 1
and u.uid = m.memberuid

OPEN @rolememberscur
FETCH NEXT FROM @rolememberscur INTO @member_name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'exec sp_addrolemember ''' + @role_name + ''', ''' + @member_name + ''''
FETCH NEXT FROM @rolememberscur INTO @member_name
END
CLOSE @rolememberscur
DEALLOCATE @rolememberscur

— PRINT @role_name
FETCH NEXT FROM @rolescur INTO @role_name
END

CLOSE @rolescur
DEALLOCATE @rolescur