Script Out DB Users & Roles/Permissions

Recently at work we were asked to provide a “clean-seed” restore of a database. Basically the database table/schema structures but without any data. The cleanseed templates we have are from the past and they do not include the DB users which were to be retained. So we needed to script out all the users and roles/permissions associated with the users. To do this a fairly simple script will be used which utilizes cursors to give us a T-SQL Statement to create each user which we can run in its entirity on the new clean seed database...

DECLARE @DatabaseUserName [sysname]

SET NOCOUNT ON

DECLARE

@errStatement [varchar](8000),

@msgStatement [varchar](8000),

@DatabaseUserID [smallint],

@ServerUserName [sysname],

@RoleName [varchar](8000),

@ObjectID [int],

@ObjectName [varchar](261)

------------------UserList Cursor---------------------------------------------------------

PRINT '-----------------------------------------------------------------------------------'

DECLARE _Users CURSOR LOCAL FORWARD_ONLY READ_ONLY

FOR

SELECT

[sysusers].[uid],

[sysusers].[name],

[master].[dbo].[syslogins].[loginname]

FROM [dbo].[sysusers]

INNER JOIN [master].[dbo].[syslogins]

ON [sysusers].[sid] = [master].[dbo].[syslogins].[sid]

WHERE [sysusers].[name] NOT IN ('dbo','guest','INFORMATION_SCHEMA','sys')

OPEN _Users

FETCH NEXT FROM _Users INTO @DatabaseUserID, @DatabaseUserName, @ServerUserName

WHILE @@FETCH_STATUS = 0

BEGIN

--------------------Create User Statement-----------------------------------------------------

BEGIN

SET @msgStatement = '--Create User: ' + @DatabaseUserName

PRINT @msgStatement

SET @msgStatement =

'CREATE USER [' + @DatabaseUserName + '] FOR LOGIN [' + @ServerUserName + ']'

+ CHAR(13)

PRINT @msgStatement

----------------------Cursor: add member to roles-------------------------------------------------

SET @msgStatement = '--Add Roles'

PRINT @msgStatement

DECLARE _sysusers CURSOR LOCAL FORWARD_ONLY READ_ONLY

FOR

SELECT

[name]

FROM [dbo].[sysusers]

WHERE

[uid] IN

(

SELECT

[groupuid]

FROM [dbo].[sysmembers]

WHERE [memberuid] = @DatabaseUserID

)

OPEN _sysusers FETCH NEXT FROM _sysusers INTO @RoleName

WHILE @@FETCH_STATUS = 0

BEGIN

SET @msgStatement = 'EXEC [sp_addrolemember]' + CHAR(13) +

CHAR(9) + '@rolename = ' + '''' + @RoleName + '''' + ',' + CHAR(13) +

CHAR(9) + '@membername = ' + '''' + @DatabaseUserName + ''''

PRINT @msgStatement

FETCH NEXT FROM _sysusers

INTO @RoleName

END

CLOSE _sysusers

DEALLOCATE _sysusers

----------------------Object Specific Permissions--------------------------------------------------

SET @msgStatement = CHAR(13) + '--Set Object Specific Permissions'

PRINT @msgStatement

DECLARE _sysobjects CURSOR LOCAL FORWARD_ONLY READ_ONLY

FOR

SELECT

DISTINCT([sysobjects].[id]),

'[' + USER_NAME([sysobjects].[uid]) + '].[' + [sysobjects].[name] + ']'

FROM [dbo].[sysprotects]

INNER JOIN [dbo].[sysobjects]

ON [sysprotects].[id] = [sysobjects].[id]

WHERE [sysprotects].[uid] = @DatabaseUserID

OPEN _sysobjects

FETCH NEXT FROM _sysobjects

INTO @ObjectID, @ObjectName

WHILE @@FETCH_STATUS = 0

BEGIN

SET @msgStatement = ''

IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 193 AND [protecttype] = 205)

SET @msgStatement = @msgStatement + 'SELECT,'

IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 195 AND [protecttype] = 205)

SET @msgStatement = @msgStatement + 'INSERT,'

IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 197 AND [protecttype] = 205)

SET @msgStatement = @msgStatement + 'UPDATE,'

IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 196 AND [protecttype] = 205)

SET @msgStatement = @msgStatement + 'DELETE,'

IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 224 AND [protecttype] = 205)

SET @msgStatement = @msgStatement + 'EXECUTE,'

IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 26 AND [protecttype] = 205)

SET @msgStatement = @msgStatement + 'REFERENCES,'

IF LEN(@msgStatement) > 0

BEGIN

IF RIGHT(@msgStatement, 1) = ','

SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) -1)

SET @msgStatement = 'GRANT' + CHAR(13) +

CHAR(9) + @msgStatement + CHAR(13) +

CHAR(9) + 'ON ' + @ObjectName + CHAR(13) +

CHAR(9) + 'TO ' + @DatabaseUserName

PRINT @msgStatement

END

SET @msgStatement = ''

IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 193 AND [protecttype] = 206)

SET @msgStatement = @msgStatement + 'SELECT,'

IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 195 AND [protecttype] = 206)

SET @msgStatement = @msgStatement + 'INSERT,'

IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 197 AND [protecttype] = 206)

SET @msgStatement = @msgStatement + 'UPDATE,'

IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 196 AND [protecttype] = 206)

SET @msgStatement = @msgStatement + 'DELETE,'

IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 224 AND [protecttype] = 206)

SET @msgStatement = @msgStatement + 'EXECUTE,'

IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 26 AND [protecttype] = 206)

SET @msgStatement = @msgStatement + 'REFERENCES,'

IF LEN(@msgStatement) > 0

BEGIN

IF RIGHT(@msgStatement, 1) = ','

SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) -1)

SET @msgStatement = 'DENY' + CHAR(13) +

CHAR(9) + @msgStatement + CHAR(13) +

CHAR(9) + 'ON ' + @ObjectName + CHAR(13) +

CHAR(9) + 'TO ' + @DatabaseUserName

PRINT @msgStatement

END

FETCH NEXT FROM _sysobjects

INTO @ObjectID, @ObjectName

END

CLOSE _sysobjects

DEALLOCATE _sysobjects

END

PRINT '-----------------------------------------------------------------------------------'

FETCH NEXT FROM _Users INTO @DatabaseUserID, @DatabaseUserName, @ServerUserName

END

CLOSE _Users

DEALLOCATE _Users

Below this portion shows an example of what the script will produce. A similar statement would get created for each user in the database you ran the script in...

--Create User: LANAKeac1

CREATE USER [LANAKeac1] FOR LOGIN [LANAKeac]

--Add Roles

EXEC [sp_addrolemember]

@rolename = 'db_datareader',

@membername = 'LANAKeac1'

EXEC [sp_addrolemember]

@rolename = 'db_datawriter',

@membername = 'LANAKeac1'

--Set Object Specific Permissions

GRANT

SELECT,INSERT

ON [dbo].[CustTr]

TO LANAKeac1