script toscript out the user id and the permission

Question

Hi ,

I require a small help. I have to restore a database .Prior to that i want all the user for that particular database  along with their rites to be scripted out so that ones we recover the  database we can use that  script to reinstate them .

Pls help

Answer 1

Hello sammy,

all database permissions of the users are stored within the database and when you restore the database all permissions are already set as before, so what/why you want to script them, additional?

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 2

Try this sammy

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_ClonePermission]    Script Date: 08/13/2012 11:43:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_ClonePermission] (@OriginalUser [sysname], @CloneUser [sysname])
AS BEGIN
SET NOCOUNT ON;
DECLARE  @First			[smallint]
		,@Last			[smallint]
		,@ErrorText		[varchar](256)
		,@SQLStatement	[varchar](max)
--Validating store procedure parameters
SET @ErrorText =   'Invalid argument was supplied for @OriginalUser parameter.'
IF @OriginalUser IS NULL
	BEGIN
		RAISERROR (@ErrorText
				  ,11
				  ,1
				  ,@OriginalUser)  
	END
--Validating store procedure parameters
SET @ErrorText =   'Invalid argument was supplied for @CloneUser parameter.'
IF @CloneUser IS NULL
	BEGIN
		RAISERROR (@ErrorText
				  ,11
				  ,1
				  ,@CloneUser)  
	END
--Creating temporary tables.
IF OBJECT_ID('Tempdb.dbo.#SQLStatementsStore') IS NOT NULL  
	DROP TABLE #SQLStatementsStore 
CREATE TABLE #SQLStatementsStore ([SQLStatementID]	[smallint] IDENTITY (1, 1) 
								 ,[SQLstatement]	[nvarchar](1024))
PRINT	'--Cloning permissions from' + SPACE(1) + QUOTENAME(@OriginalUser) + SPACE(1) 
		+ 'to' + SPACE(1) + QUOTENAME(@CloneUser)
PRINT '--------------------------------------------------------------------------------------------------'
IF @CloneUser LIKE '%\%'
BEGIN
	-- Generate SQL command to DROP clone user login from the SQL instance if it exists already. 
	INSERT INTO #SQLStatementsStore (SQLStatement) 
	SELECT 'IF  EXISTS (SELECT * FROM [sys].[server_principals] WHERE [name] = N''' + @CloneUser 
			+ ''') DROP LOGIN ' + QUOTENAME(@CloneUser)
	--Generate SQL command to CREATE clone user login on SQL instance 
	INSERT INTO #SQLStatementsStore (SQLStatement) 
	SELECT 'CREATE LOGIN [' + @CloneUser + '] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]'
END
--Generate SQL command to clone server role membership of original login to clone login
INSERT INTO #SQLStatementsStore (SQLStatement)
SELECT 'EXEC [master]..[sp_addsrvrolemember] @loginame = N''' + @CloneUser  + ''', @rolename = N''' + sp.name  + ''''
FROM [master].[sys].[server_role_members] rm
INNER JOIN [master].[sys].[server_principals] sp
ON sp.[principal_id] = rm.[role_principal_id]
INNER JOIN [master].[sys].[server_principals] lp
ON lp.[principal_id] = rm.[member_principal_id]
WHERE lp.[type] IN ('U', 'G', 'S')
AND lp.[name] = @OriginalUser
AND lp.[name] NOT LIKE '##%'
AND sp.[type] = 'R'
--Generate SQL command to DROP clone user database user from database if it exists already. 
INSERT INTO #SQLStatementsStore (SQLStatement) 
SELECT	'IF  EXISTS (SELECT * FROM [sys].[database_principals] WHERE [name] = N''' + @CloneUser + ''') DROP USER ' + QUOTENAME(@CloneUser)
--Generate SQL command to CREATE clone user database user.
INSERT INTO #SQLStatementsStore (SQLStatement) 
SELECT 'CREATE USER ' + QUOTENAME(@CloneUser) + ' FOR LOGIN ' + QUOTENAME(@CloneUser) + '  WITH DEFAULT_SCHEMA=[dbo]'
--Generate SQL commands for database memberships.
INSERT INTO #SQLStatementsStore (SQLStatement) 
SELECT 'USE' + SPACE(1) + QUOTENAME(DB_NAME()) + ';' + 'EXEC [sp_addrolemember] N' 
		+ QUOTENAME(USER_NAME(rm.[role_principal_id]), '''') + ', N' + QUOTENAME(@CloneUser, '''') 
FROM [sys].[database_role_members] AS rm
WHERE USER_NAME(rm.[member_principal_id]) IN (SELECT dp.[name] AS [DatabaseUser]
											  FROM [sys].[syslogins] sl
											  INNER JOIN [sys].[database_principals] dp
											  ON sl.[sid] = dp.[sid]
											  WHERE sl.[name] = @OriginalUser) 
ORDER BY rm.[role_principal_id] ASC
--Generate SQL commands for clone database user object level permissions
INSERT INTO #SQLStatementsStore (SQLStatement) 
SELECT	CASE WHEN perm.[state] <> 'W' THEN perm.[state_desc] ELSE 'GRANT' END
	+ SPACE(1) + perm.[permission_name] + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.[name]) 
	+ CASE WHEN cl.[column_id] IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.[name]) + ')' END
	+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@CloneUser) COLLATE DATABASE_DEFAULT
	+ CASE WHEN perm.[state] <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END 
FROM [sys].[database_permissions] AS perm
INNER JOIN [sys].[objects] AS obj
ON perm.[major_id] = obj.[object_id]
INNER JOIN sys.[database_principals] AS usr
ON perm.[grantee_principal_id] = usr.[principal_id]
LEFT JOIN sys.[columns] AS cl
ON cl.[column_id] = perm.[minor_id] AND cl.[object_id] = perm.[major_id]
WHERE	usr.[name] = @OriginalUser
ORDER BY perm.[permission_name] ASC, perm.[state_desc] ASC
--Generate SQL commands for clone database user database level permissions
INSERT INTO #SQLStatementsStore (SQLStatement) 
SELECT	CASE WHEN perm.[state] <> 'W' THEN perm.[state_desc] ELSE 'GRANT' END
	+ SPACE(1) + perm.[permission_name] + SPACE(1)
	+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@CloneUser) COLLATE DATABASE_DEFAULT
	+ CASE WHEN perm.[state] <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Database Level Permissions'
FROM sys.[database_permissions] AS perm
INNER JOIN sys.[database_principals] AS usr
ON perm.[grantee_principal_id] = usr.[principal_id]
WHERE usr.[name] = @OriginalUser
AND	perm.[major_id] = 0
ORDER BY perm.[permission_name] ASC, perm.[state_desc] ASC
--Generate SQL command to clone SQL instance level permissions of original login to clone login
INSERT INTO #SQLStatementsStore (SQLStatement)
SELECT 'USE [master]; GRANT ' + sp.permission_name + ' TO ' + QUOTENAME(@CloneUser)
FROM [master].[sys].[server_principals] p
INNER JOIN [master].[sys].[server_permissions] sp
ON sp.[grantee_principal_id] = p.[principal_id]
LEFT JOIN [master].[sys].[server_principals] p2
ON p2.[principal_id] = sp.[major_id] AND sp.[class] = 101
LEFT JOIN [master].[sys].[endpoints] e
ON e.[endpoint_id] = sp.[major_id] AND sp.[class] = 105
WHERE p.[type] In ('U', 'G', 'S')
AND p.[name] = @OriginalUser
AND sp.state_desc <> 'GRANT_WITH_GRANT_OPTION'
INSERT INTO #SQLStatementsStore (SQLStatement)
SELECT 'USE [master]; GRANT ' + sp.permission_name + ' TO ' + QUOTENAME(@CloneUser) + ' WITH GRANT OPTION'
FROM [master].[sys].[server_principals] p
INNER JOIN [master].[sys].[server_permissions] sp
ON sp.[grantee_principal_id] = p.[principal_id]
LEFT JOIN [master].[sys].[server_principals] p2
ON p2.[principal_id] = sp.[major_id] AND sp.[class] = 101
LEFT JOIN [master].[sys].[endpoints] e
ON e.[endpoint_id] = sp.[major_id] AND sp.[class] = 105
WHERE p.[type] In ('U', 'G', 'S')
AND p.[name] = @OriginalUser
AND sp.state_desc = 'GRANT_WITH_GRANT_OPTION'
SELECT * FROM #SQLStatementsStore
PRINT '--Prepairing to execute commands to create clone SQL user: '
PRINT '--------------------------------------------------------------------------------------------------'
SELECT @First = MIN([SQLStatementID]) FROM #SQLStatementsStore
SELECT @Last = MAX([SQLStatementID]) FROM #SQLStatementsStore
WHILE @First <= @Last
BEGIN
	SELECT @SQLStatement = [SQLStatement]
	FROM #SQLStatementsStore
	WHERE [SQLStatementID] = @First
	PRINT @SQLStatement
	EXEC (@SQLStatement)
	SET @First = @First + 1
END
SET NOCOUNT OFF;
END
--

---

Regards,

Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

http://basitaalishan.com

Answer 3

To list server level permission use:

WITH CTE_Role (name,role,type_desc)
AS
(SELECT PRN.name,
srvrole.name AS [role] , 
Prn.Type_Desc 
FROM sys.server_role_members membership 
INNER JOIN (SELECT * FROM sys.server_principals  WHERE type_desc='SERVER_ROLE') srvrole 
ON srvrole.Principal_id= membership.Role_principal_id 
RIGHT JOIN sys.server_principals  PRN 
ON PRN.Principal_id= membership.member_principal_id WHERE Prn.Type_Desc NOT IN ('SERVER_ROLE') AND PRN.is_disabled =0
UNION ALL
SELECT p.[name], 'ControlServer' ,p.type_desc AS loginType FROM sys.server_principals p 
  JOIN sys.server_permissions Sp
   ON p.principal_id = sp.grantee_principal_id WHERE sp.class = 100 
  AND sp.[type] = 'CL' 
  AND state = 'G' )
SELECT 
name,
Type_Desc ,
CASE WHEN [public]=1 THEN 'Y' ELSE 'N' END AS 'Public',
CASE WHEN [sysadmin] =1 THEN 'Y' ELSE 'N' END AS 'SysAdmin' ,
CASE WHEN [securityadmin] =1 THEN 'Y' ELSE 'N' END AS 'SecurityAdmin',
CASE WHEN [serveradmin] =1 THEN 'Y' ELSE 'N' END AS 'ServerAdmin',
CASE WHEN [setupadmin] =1 THEN 'Y' ELSE 'N' END AS 'SetupAdmin',
CASE WHEN [processadmin] =1 THEN 'Y' ELSE 'N' END AS 'ProcessAdmin',
CASE WHEN [diskadmin] =1 THEN 'Y' ELSE 'N' END AS 'DiskAdmin',
CASE WHEN [dbcreator] =1 THEN 'Y' ELSE 'N' END AS 'DBCreator',
CASE WHEN [bulkadmin] =1 THEN 'Y' ELSE 'N' END AS 'BulkAdmin' ,
CASE WHEN [ControlServer] =1 THEN 'Y' ELSE 'N' END AS 'ControlServer' 
FROM CTE_Role 
PIVOT(
COUNT(role) FOR role IN ([public],[sysadmin],[securityadmin],[serveradmin],[setupadmin],[processadmin],[diskadmin],[dbcreator],[bulkadmin],[ControlServer])
) AS pvt WHERE Type_Desc NOT IN ('SERVER_ROLE')ORDER BY name,type_desc 
--EXEC sys.sp_MS_marksystemobject sp_ClonePermission
--GO

To list database level permission use:

SET NOCOUNT ON
CREATE TABLE #DatabaseRoleMemberShip 
   (
        Username VARCHAR(100),
        Rolename VARCHAR(100),
        Databasename VARCHAR(100)
         
    )DECLARE @Cmd AS VARCHAR(MAX)DECLARE @PivotColumnHeaders VARCHAR(4000)           SET @Cmd = 'USE [?] ;insert into #DatabaseRoleMemberShip 
select u.name,r.name,''?'' from sys.database_role_members RM inner join 
sys.database_principals U on U.principal_id=RM.member_principal_id
inner join sys.database_principals R on R.principal_id=RM.role_principal_id
where u.type<>''R'''EXEC sp_MSforeachdb @command1=@cmd
SELECT  @PivotColumnHeaders =                         
  COALESCE(@PivotColumnHeaders + ',[' + CAST(rolename AS VARCHAR(MAX)) + ']','[' + CAST(rolename AS VARCHAR(MAX))+ ']'                     
  )                     
  FROM (SELECT DISTINCT rolename FROM #DatabaseRoleMemberShip )a ORDER BY rolename  ASC
SET @Cmd = 'select 
databasename,username,'+@PivotColumnHeaders+'
from 
(
select   * from #DatabaseRoleMemberShip) as p
pivot 
(
count(rolename  )
for rolename in ('+@PivotColumnHeaders+') )as pvt'EXECUTE(@Cmd )        DROP TABLE #DatabaseRoleMemberShip 

Source: I got these queries from here: http://www.sqlservercentral.com/blogs/practicalsqldba/2012/08/05/sql-server-list-logins-database-and-server-level-role-permission/

---

Regards,

Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

http://basitaalishan.com

Answer 4

I think in your case you need to run the following store procedure to fix logins:

USE <UserDatabase>
GO
Exec sp_change_users_login 'update_one', '<Specifyuser>', '<SpecifySQLLoginName'

---

Regards,

Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

http://basitaalishan.com

Answer 5

Check : http://mssqlfun.wordpress.com/2012/07/18/genrate-database-user-script-with-access-rights/

Try below script for the same...

SET NOCOUNT ON 
GO

CREATE TABLE #DBROLE (DBNAME VARCHAR(100), DBROLE VARCHAR (100), DBROLE_MEMBER VARCHAR(100), 
 SID VARBINARY(85), DEFAULT_SCHEMA_NAME VARCHAR(100), LOGIN_NAME VARCHAR(100), DB_PRINCIPAL_ID INT)
  
INSERT INTO #DBROLE
 SELECT DB_NAME() DBNAME, C.NAME, B.NAME, B.SID, B.DEFAULT_SCHEMA_NAME, D.NAME LOGINNAME, B.PRINCIPAL_ID AS LOGIN_NAME 
 FROM SYS.DATABASE_ROLE_MEMBERS A 
 INNER JOIN SYS.DATABASE_PRINCIPALS B ON A.MEMBER_PRINCIPAL_ID = B.PRINCIPAL_ID
 INNER JOIN SYS.DATABASE_PRINCIPALS C ON A.ROLE_PRINCIPAL_ID = C.PRINCIPAL_ID
 LEFT JOIN SYS.SERVER_PRINCIPALS D ON B.SID = D.SID  where B.NAME <> 'dbo'

ALTER TABLE #DBROLE ADD ID INT IDENTITY(1,1)

DECLARE @COUNTER INT, @MAXID2 INT, @LOGIN_DBROLE VARCHAR(MAX) 
SELECT @MAXID2 = MAX(ID) FROM #DBROLE
SET @COUNTER = 1
 
WHILE @COUNTER <= @MAXID2
BEGIN

SELECT @LOGIN_DBROLE  = 'USE ['+DBNAME+']
GO
IF NOT EXISTS (SELECT * FROM SYS.DATABASE_PRINCIPALS WHERE NAME = '''+DBROLE_MEMBER+''')
BEGIN 
          CREATE USER ['+DBROLE_MEMBER+'] 
    FOR LOGIN ['+LOGIN_NAME+']'+ISNULL(' WITH DEFAULT_SCHEMA=['+DEFAULT_SCHEMA_NAME+']','')+'
END
ALTER USER ['+DBROLE_MEMBER+'] WITH LOGIN = ['+LOGIN_NAME+']
EXEC SP_ADDROLEMEMBER '''+DBROLE+''','''+DBROLE_MEMBER+'''
GO
' FROM #DBROLE WHERE ID = @COUNTER

SELECT @COUNTER = @COUNTER + 1 
PRINT @LOGIN_DBROLE

END

DROP TABLE #DBROLE