October 29, 2010 at 11:52 am
I'm looking for a script that extracts the code to create users and granted roles per database. I've found partials of either or, logins, lists, create scripts for logins, but not db users and their roles. Does anyone have a good source for this? Any help appreciated!
Thank you!
¤ §unshine ¤
October 29, 2010 at 12:57 pm
http://www.sql-server-performance.com/articles/dba/object_permission_scripts_p1.aspx
i used this script few days back. this scripts permissions for a single user. you might need to wrap the code in a loop or cursor to extract data for all users.
November 1, 2010 at 8:26 am
Great! Let me see what I can figure out... thank you so much!
¤ §unshine ¤
November 1, 2010 at 10:45 am
I wrote this for a recent project, does this help?
SELECT 'IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = '+ name + ') ' + char(13)
+ 'CREATE USER [' + CONVERT(char(20), name + ']') + ' FOR LOGIN [' + CONVERT(char(20), name + ']') + ' WITH DEFAULT_SCHEMA = dbo'
FROM sys.database_principals WHERE type IN ('S', 'U') and principal_id > 4
SELECT 'EXEC sys.sp_addrolemember @rolename = ' + CONVERT(char(25), b.name) + ' , @membername = ' + CONVERT(char(20), c.name)
from sys.database_role_members a JOIN sys.database_principals b on a.role_principal_id = b.principal_id
JOIN sys.database_principals c on a.member_principal_id = c.principal_id
WHERE b.type = 'R' and c.name != 'dbo'
ORDER by b.name, c.name
November 2, 2010 at 12:14 am
Looks nice. however it wont script object level permissions.
November 2, 2010 at 7:19 am
This one should work- I posted on another forum post a while back but can't find the link. You'd need to run it on each database you are scripting permissions for, but it will get object level perms and role memberships.
Should get you everything you need.
Regards,
Steve
/*
This script will script the role members for all roles on the database.
This is useful for scripting permissions in a development environment before refreshing
development with a copy of production. This will allow us to easily ensure
development permissions are not lost during a prod to dev restoration.
*/
/*********************************************/
/********* DB CONTEXT STATEMENT *********/
/*********************************************/
SELECT '-- [-- DB CONTEXT --] --' AS [-- SQL STATEMENTS --],
1 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS [-- SQL STATEMENTS --],
1 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT '' AS [-- SQL STATEMENTS --],
2 AS [-- RESULT ORDER HOLDER --]
UNION
/*********************************************/
/********* DB ROLE PERMISSIONS *********/
/*********************************************/
SELECT '-- [-- DB ROLES --] --' AS [-- SQL STATEMENTS --],
3 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT'EXEC sp_addrolemember @rolename ='
+ SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1) + QUOTENAME(USER_NAME(rm.member_principal_id), '''') AS [-- SQL STATEMENTS --],
3 AS [-- RESULT ORDER HOLDER --]
FROMsys.database_role_members AS rm
WHEREUSER_NAME(rm.member_principal_id) IN (
--get user names on the database
SELECT [name]
FROM sys.database_principals
WHERE [principal_id] > 4 -- 0 to 4 are system users/schemas
and [type] IN ('G', 'S', 'U') -- S = SQL user, U = Windows user, G = Windows group
)
--ORDER BY rm.role_principal_id ASC
UNION
SELECT '' AS [-- SQL STATEMENTS --],
4 AS [-- RESULT ORDER HOLDER --]
UNION
/*********************************************/
/********* OBJECT LEVEL PERMISSIONS *********/
/*********************************************/
SELECT '-- [-- OBJECT LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
5 AS [-- RESULT ORDER HOLDER --]
UNION
SELECTCASE
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) --select, execute, etc on specific objects
+ CASE
WHEN cl.column_id IS NULL THEN SPACE(0)
ELSE '(' + QUOTENAME(cl.name) + ')'
END
+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default
+ CASE
WHEN perm.state <> 'W' THEN SPACE(0)
ELSE SPACE(1) + 'WITH GRANT OPTION'
END
AS [-- SQL STATEMENTS --],
5 AS [-- RESULT ORDER HOLDER --]
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
--WHEREusr.name = @OldUser
--ORDER BY perm.permission_name ASC, perm.state_desc ASC
UNION
SELECT '' AS [-- SQL STATEMENTS --],
6 AS [-- RESULT ORDER HOLDER --]
UNION
/*********************************************/
/********* DB LEVEL PERMISSIONS *********/
/*********************************************/
SELECT '-- [--DB LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
7 AS [-- RESULT ORDER HOLDER --]
UNION
SELECTCASE
WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option
ELSE 'GRANT'
END
+ SPACE(1) + perm.permission_name --CONNECT, etc
+ SPACE(1) + 'TO' + SPACE(1) + '[' + USER_NAME(usr.principal_id) + ']' COLLATE database_default --TO <user name>
+ CASE
WHEN perm.state <> 'W' THEN SPACE(0)
ELSE SPACE(1) + 'WITH GRANT OPTION'
END
AS [-- SQL STATEMENTS --],
7 AS [-- RESULT ORDER HOLDER --]
FROMsys.database_permissions AS perm
INNER JOIN
sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
--WHEREusr.name = @OldUser
WHERE[perm].[major_id] = 0
AND [usr].[principal_id] > 4 -- 0 to 4 are system users/schemas
AND [usr].[type] IN ('G', 'S', 'U') -- S = SQL user, U = Windows user, G = Windows group
ORDER BY [-- RESULT ORDER HOLDER --]
November 2, 2010 at 7:27 am
you can submit the script in the scripts section so that it is easier to find out later on this forum.
November 2, 2010 at 7:36 am
We were thinking the same way! - I submitted it immediately after I re-posted.
Thanks!
Steve
November 2, 2010 at 12:28 pm
March 28, 2013 at 8:57 am
Hi
I am looking for same script to generate script for User DB roles . Can you share if you have it now?
Or if anyone have it can share ?
Thanks
April 3, 2013 at 7:12 pm
logicinside22 (3/28/2013)
HiI am looking for same script to generate script for User DB roles . Can you share if you have it now?
Or if anyone have it can share ?
Thanks
I think the script I have posted here [/url] should work. I just created an empty DB, added a table, added a new role, and issued a "grant select on dbo.table1 to test_role" and then executed the script.
The output I got back was under object level permissions for that role:
-- [-- OBJECT LEVEL PERMISSIONS --] --
GRANT SELECT ON [dbo].[table1] TO [test_role1]
Check that out and let me know if something is missing there.
Cheers,
Steve
November 29, 2015 at 4:18 am
this will script out everything
http://dbasqlhelp.blogspot.in/2015/11/extract-all-database-permissions-before.html
remove the last insert which will fix orphan users
January 28, 2016 at 4:03 am
https://gallery.technet.microsoft.com/Extract-Database-dfa53d5a
that might help you
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply