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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.