question

ManeeshArora-7050 avatar image
0 Votes"
ManeeshArora-7050 asked ·

permissions to copy database role to another sql server


SET NOCOUNT ON
go

select 'USE ' + db_name() + CHAR(10) + CHAr(13) + ' GO'
go

--Role Memberships'

SELECT --rm.role_principal_id,
'EXEC sp_addrolemember @rolename ='
+ SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''')
+ ', @membername =' + SPACE(1) + QUOTENAME(USER_NAME(rm.member_principal_id), '''') AS '--Role Memberships'
FROM sys.database_role_members AS rm
ORDER BY rm.role_principal_id




--Object Level Permissions'
SELECT
CASE WHEN perm.state != 'W' THEN perm.state_desc ELSE 'GRANT' END + SPACE(1) +
perm.permission_name + SPACE(1) + 'ON '+ QUOTENAME(Schema_NAME(obj.schema_id)) + '.'
+ QUOTENAME(obj.name) collate Latin1_General_CI_AS_KS_WS
+ CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END
+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(usr.name)
+ CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Object Level Permissions'
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
ORDER BY usr.name



--Database Level Permissions'
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(usr.name) 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 = @OldUser
--AND
perm.major_id = 0
ORDER BY perm.permission_name ASC, perm.state_desc ASC

sql-server-general
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ·

So what do you want us to do? Review the around the 150 lines of code you have written? Without telling us what is supposed to do?

Did you test the code before you posted it?

Quickly browsing the code, I make two observations:

1) This 'CREATE ROLE [' + @roleName + ']' should be CREATE ROLE + quotename(@roleName), The quotename function will but brackets around the name - and double any right brackets there are in the name. This protects you against SQL injection. You have quite a few of hardcoded brackets in your script.

2) I see there is a whole lot of GO. This is OK, if the generated code is intended to be executed manually, but if you plan to put this in an SQL string and run, it is not going to fly. GO is not an SQL statement, but it is an instruction to a query tool like SSMS to break up the script in batches.

· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


SET NOCOUNT ON
go

select 'USE ' + db_name() + CHAR(10) + CHAr(13) + ' GO'
go

--Role Memberships'

SELECT --rm.role_principal_id,
'EXEC sp_addrolemember @rolename ='
+ SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''')
+ ', @membername =' + SPACE(1) + QUOTENAME(USER_NAME(rm.member_principal_id), '''') AS '--Role Memberships'
FROM sys.database_role_members AS rm
ORDER BY rm.role_principal_id


0 Votes 0 ·