May 23, 2008 at 6:00 pm
Plz use the below mentioned code for scripting out user level permissions:
--Generates Database object permissions for SQL 2000.
SELECT user_name(p.grantor) AS GRANTOR ,
user_name(p.uid) AS GRANTEE,
db_name() AS TABLE_CATALOG ,
user_name(o.uid) AS TABLE_SCHEMA ,
o.name AS TABLE_NAME,
CASE p.action
WHEN 26 THEN 'REFERENCES'
WHEN 193 THEN 'SELECT'WHEN 195 THEN
'INSERT'
WHEN 196 THEN 'DELETE'
WHEN 197 THEN 'UPDATE'
WHEN 224 THEN 'EXECUTE'
else 'test' end AS PRIVILEGE_TYPE,
o.xtype,
CASE WHEN p.protecttype = 205 THEN 'NO' ELSE
'YES'
END AS IS_GRANTABLE,
'grant ' + CASE p.action
WHEN 26 THEN 'REFERENCES'
WHEN 193 THEN 'SELECT'
WHEN 195 THEN 'INSERT'
WHEN 196 THEN 'DELETE'
WHEN 197 THEN 'UPDATE'
WHEN 224 THEN 'EXECUTE'
ELSE 'test' END + ' on [' +
user_name(o.uid) + '].[' + o.name + '] to [' + user_name(p.uid) + ']'
FROM sysprotects p,
sysobjects o
WHERE (p.protecttype = 204 OR /*grant exists without same grant with
grant
*/
(p.protecttype = 205
AND NOT EXISTS (SELECT *
FROM sysprotects
p2
WHERE p2.id =
p.id
AND
p2.uid = p.uid
AND
p2.action = p.action
AND
p2.columns = p.columns
AND
p2.grantor = p.grantor
AND
p2.protecttype = 204)))
AND p.action in (26,193,195,196,197,224)
AND p.id = o.id
AND 0 != (permissions(o.id))
ORDER BY table_name
SQL Server 2005
--SQL 2005 ONLY
--This script will generate all user permissions and the code to
recreate them as required.
SELECT C.name 'Schema',
B.name AS Object,
D.name username,
A.type permissions_type,
A.permission_name,
A.state permission_state,
A.state_desc,
state_desc
+ ' ' + permission_name
+ ' ON ['+ C.name
+ '].[' + B.name + '] to ['
+ D.name
+ ']' COLLATE LATIN1_General_CI_AS AS
Generated_Code
FROM sys.database_permissions AS A JOIN sys.objects AS B ON
A.major_id =
B.object_id
JOIN sys.schemas AS C ON B.schema_id =
C.schema_id
JOIN sys.database_principals AS D ON
A.grantee_principal_id = D.principal_id
ORDER BY 1, 2, 3, 5
Hope this helps!
Manu Jaidka
May 20, 2010 at 12:32 pm
Script out the users from Master. Run them into the new server. Then restore the DB.
Orphaned users can be fixed with SP_change_Users_login
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply