Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database User and Roles Script Extract Needed


Database User and Roles Script Extract Needed

Author
Message
sunshine-587009
sunshine-587009
Old Hand
Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)

Group: General Forum Members
Points: 365 Visits: 1273
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 ¤
ps.
ps.
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2257 Visits: 3668
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.



Pradeep Singh
sunshine-587009
sunshine-587009
Old Hand
Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)

Group: General Forum Members
Points: 365 Visits: 1273
Great! Let me see what I can figure out... thank you so much!

¤ §unshine ¤
raistlinx
raistlinx
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 438
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


ps.
ps.
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2257 Visits: 3668
Looks nice. however it wont script object level permissions.



Pradeep Singh
S. Kusen
S. Kusen
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1092 Visits: 1108
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 --]
FROM   sys.database_role_members AS rm
WHERE   USER_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
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) --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
--WHERE   usr.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
SELECT   CASE
         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 --]
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

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 --]


ps.
ps.
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2257 Visits: 3668
you can submit the script in the scripts section so that it is easier to find out later on this forum.



Pradeep Singh
S. Kusen
S. Kusen
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1092 Visits: 1108
We were thinking the same way! - I submitted it immediately after I re-posted.

Thanks!
Steve
MysteryJimbo
MysteryJimbo
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1348 Visits: 15327
Idera also do a free tool if you are able to install it.

Idera Permissions 2.0
logicinside22
logicinside22
Old Hand
Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)

Group: General Forum Members
Points: 328 Visits: 1387
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

Aim to inspire rather than to teach.
SQL Server DBA
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search