Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

permissions Expand / Collapse
Author
Message
Posted Monday, September 14, 2009 5:00 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 10:22 PM
Points: 432, Visits: 1,075
we have a db , 200 users are in that db.
we need to restore that db.so how to take the backup of all user permissions .
i should check that all permissions after restore.
so how to take the backup of all permissions
Post #787821
Posted Tuesday, September 15, 2009 9:17 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 11:34 AM
Points: 4,064, Visits: 5,366
Restoring a database from a backup restores everything in the database, including users and permissions.

If you need to copy only permissions, you'll have to script them. There are several scripts available in the script section of this site, although a lot of them script the permissions for all databases rather than only one. Try searching for "script permissions" in the Search box above.


Greg
Post #788261
Posted Tuesday, September 15, 2009 4:57 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 10:22 PM
Points: 432, Visits: 1,075
thanks..........


i want to get the list of permissions of a db.

can u pls provide the script.......
Post #788621
Posted Tuesday, September 15, 2009 5:22 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 10:34 AM
Points: 25, Visits: 437
Hi,

Try out this script and run it on your database. It give the permissions of the users as output. copy it and run it on your destination DB.

SET NOCOUNT ON


DECLARE @OldUser varchar(100), @NewUser sysname

DECLARE a_cur CURSOR FOR
select name,name from sys.sysusers where uid>3 AND uid < 16384 ORDER BY
1
Open a_cur
FETCH NEXT from a_cur into @OldUser,@NewUser
--SET @NewUser = @OldUser
WHILE (@@FETCH_STATUS = 0)
BEGIN

SELECT 'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS '--Database Context'


SELECT '--Cloning permissions from' + SPACE(1) + QUOTENAME(@OldUser) + SPACE(1) + 'to' + SPACE(1) + QUOTENAME(@NewUser) AS '--Comment'


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


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(@NewUser) COLLATE database_default
+ 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
WHERE usr.name = @OldUser
ORDER BY perm.permission_name ASC, perm.state_desc ASC


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(@NewUser) 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
FETCH NEXT from a_cur into @OldUser,@NewUser
END
CLOSE a_cur
DEALLOCATE a_cur


~KKK
Post #788630
Posted Tuesday, October 28, 2014 6:59 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 4:04 AM
Points: 934, Visits: 1,530
Ok

Thanks.
Post #1629107
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse