March 28, 2015 at 6:46 pm
Mubeen Mohammed-203067 (3/13/2015)
Hi Kusen,First, Thanks for the excellent script and keep updating with the feedback is enourmous.
I just tested the script by adding 'R' in two stmts (as you mentioned in one of the post) but still I think it is not creating the User DB Roles as expected.
I would like to see these roles should be scripted as below. I do not have these roles in Production as DEV but ultimately after restoring/overwriting the existing DEV database with a copy of Production, I need to re-create these roles and then add required members into that and the object level permissions.
Example: CREATE ROLE [DevUsersOnly] AUTHORIZATION [dbo]
Thanks,
Mubeen
The script would need the role to exist, and simply will script out adding the users to that role, as it currently exists. I see your need, but I don't really know how common a situation that is. I would think that most either promote or refresh the databases between environments and keep the roles in both, just not have any users in those roles in produciton. I'll see what I can do to look at user-defined roles and re-creating them if they don't exist before scripting out the addition of users to those roles. Thanks, as always, for the feedback.
March 28, 2015 at 6:50 pm
krishna85 (3/27/2015)
Script is really good and it helps a lot.Does this script resolves the issues with Orphan Users?
If not it would be good if we include that portion in the script as well....
When you say "resolves", do you mean delete orphaned users? If so, that is not really what this script is designed to do. It is designed to script out permissions within a database to help with restoring those permissions, generally when a database is pulled down from production to a non-production environment where different permissions will be established. If a database user is not mapped to a login, there multiple reasons why a user may exist without a login. Here are two of them: 1) Users that exist in contained databases that will not be assigned with a login, and 2) users created "WITHOUT LOGIN".
March 30, 2015 at 7:26 am
Thank you!
After the database refresh, when i re-apply the permissions extracted before the refresh, I get complains from the users about connectivity issues.
This is because the SID's of the logins don't match between our two environments.
I know, I can re-create those logins and match the SID's so I don't run into this again.
Adding the orphan users fix piece to the script would be helpful.... However, I understand that is not the purpose the script was designed for....
Will get this fixed from my end.....Thanks again for this wonderful script!
May 4, 2015 at 3:49 am
hi Steve,
thanks for this awesome script, very useful!
would it be possible to add User-Defined Table Types as well? those are the only permissions now i'm missing.
see StackExchange for the example query to output the permissions.
many thanks,
regards Eddict
May 11, 2015 at 4:29 am
hi Steve,
another request;
when I grant View Definition to a role on schema level it is shown by the script, but if I grant it on database level it doesn't.
could you please add this as well?
shown in 'DB LEVEL SCHEMA PERMISSIONS' section;GRANT VIEW DEFINITION ON SCHEMA::[dbo] TO [myRole]
expected, but not shown (in 'DB LEVEL PERMISSIONS' section?);GRANT VIEW DEFINITION ON DATABASE::[MyDB] TO [myRole]
regards Eddict
May 11, 2015 at 5:38 am
Eddict (5/11/2015)
hi Steve,another request;
when I grant View Definition to a role on schema level it is shown by the script, but if I grant it on database level it doesn't.
could you please add this as well?
shown in 'DB LEVEL SCHEMA PERMISSIONS' section;
GRANT VIEW DEFINITION ON SCHEMA::[dbo] TO [myRole]expected, but not shown (in 'DB LEVEL PERMISSIONS' section?);
GRANT VIEW DEFINITION ON DATABASE::[MyDB] TO [myRole]regards Eddict
stupid me, found it myself. in the 'DB LEVEL PERMISSIONS' just add 'R' as allowed type;
AND [usr].[type] IN ('G', 'S', 'U', 'R') -- S = SQL user, U = Windows user, G = Windows group, R = Database role
May 11, 2015 at 7:17 am
Eddict (5/4/2015)
hi Steve,thanks for this awesome script, very useful!
would it be possible to add User-Defined Table Types as well? those are the only permissions now i'm missing.
see StackExchange for the example query to output the permissions.
many thanks,
regards Eddict
added it myself;
UNION
/*********************************************/
/********* TYPE LEVEL PERMISSIONS *********/
/*********************************************/
SELECT '-- [-- TYPE LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
9.1 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(SCHEMA_NAME(tp.schema_id)) + '.' + QUOTENAME(tp.name) --select, execute, etc on specific objects
+ 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 --],
9.5 AS [-- RESULT ORDER HOLDER --]
FROM
sys.database_permissions AS perm
INNER JOIN
sys.types AS tp
ON perm.major_id = tp.user_type_id
INNER JOIN
sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
May 11, 2015 at 11:15 am
Eddict (5/11/2015)
Eddict (5/4/2015)
hi Steve,thanks for this awesome script, very useful!
would it be possible to add User-Defined Table Types as well? those are the only permissions now i'm missing.
see StackExchange for the example query to output the permissions.
many thanks,
regards Eddict
added it myself;
UNION
/*********************************************/
/********* TYPE LEVEL PERMISSIONS *********/
/*********************************************/
SELECT '-- [-- TYPE LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
9.1 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(SCHEMA_NAME(tp.schema_id)) + '.' + QUOTENAME(tp.name) --select, execute, etc on specific objects
+ 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 --],
9.5 AS [-- RESULT ORDER HOLDER --]
FROM
sys.database_permissions AS perm
INNER JOIN
sys.types AS tp
ON perm.major_id = tp.user_type_id
INNER JOIN
sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
Many thanks, Eddict. I apologize that I didn't have the time available to review this myself. I'll get this updated on the main form as well and be sure to give you kudos, unless you don't want your name showing in the main script. I like to give credit where credit is due.
Regards,
Steve
May 13, 2015 at 12:17 am
S. Kusen (5/11/2015)
Many thanks, Eddict. I apologize that I didn't have the time available to review this myself. I'll get this updated on the main form as well and be sure to give you kudos, unless you don't want your name showing in the main script. I like to give credit where credit is due.Regards,
Steve
hi Steve, no problem! you might want to review my 2 additions and adjust it wherever needed.
regards Eddict
June 30, 2015 at 6:34 am
Hey, guess this should work for mapping orphaned users back to their logins but doesn't create missing logins. (Because it's not on the db level anymore)
This works from 2k5SP2 otherwise it won't. See this post
/*********************************************/
/********* MAP ORPHANED USERS *********/
/*********************************************/
SELECT '-- [-- ORPHANED USERS --] --' AS [-- SQL STATEMENTS --],
3 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT'ALTER USER [' + rm.name + '] WITH LOGIN = [' + rm.name + ']',
4
FROMsys.database_principals AS rm
Inner JOIN sys.server_principals as sp
ON rm.name = sp.name and rm.sid <> sp.sid
WHERE rm.[type] IN ('U', 'S', 'G') -- windows users, sql users, windows groups
AND rm.name NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys', 'MS_DataCollectorInternalUser')
UNION
Furthermore I've changed the user creation script - if there was no default schema set there were no rows returned so as a fallback I've put dbo as the default schema if it is missing. Also used the respective LOGIN name for the FOR LOGIN clause in the create user statement if there a different names used.
/*********************************************/
/********* DB USER CREATION *********/
/*********************************************/
SELECT '-- [-- DB USERS --] --' AS [-- SQL STATEMENTS --],
5 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT'IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = ''' + rm.[name] + '''' + ') BEGIN CREATE USER ' +
QUOTENAME(rm.[name]) + ' FOR LOGIN ' + QUOTENAME(sp.[name]) + ' WITH DEFAULT_SCHEMA = ' + QUOTENAME(IsNull(rm.default_schema_name, 'dbo')) + ' END;',
6 AS [-- RESULT ORDER HOLDER --]
FROMsys.database_principals AS rm
LEFT JOIN sys.server_principals as sp
ON rm.sid = sp.sid
WHERE rm.[type] IN ('U', 'S', 'G') -- windows users, sql users, windows groups
AND rm.name NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys', 'MS_DataCollectorInternalUser')
August 18, 2015 at 1:28 am
Thanks I've found this script very useful.
However the last update for contained databases seems to have broken the script for SQL2005 & SQL2008.
Line 63 has a reference to column "authentication_type" in sys.database_principals , which does not exist in the older versions of SQL.
Msg 207, Level 16, State 1, Line 63
Invalid column name 'authentication_type'.
I'm not sure how to get around the issue other than commenting out the contained database code.
August 18, 2015 at 1:48 am
I also get a collation conflict on line 85 for databases like sharepoint which have a different collation to server default.
I've found it works fine when changing line 85 from
ON rm.name = sp.name and rm.sid <> sp.sid
to
ON rm.name = sp.name COLLATE DATABASE_DEFAULT and rm.sid <> sp.sid
August 18, 2015 at 5:14 am
Thanks for the feedback. I will review and get an update posted.
August 20, 2015 at 3:08 pm
Thanks for making this script. It looks really nice.
V3 that is posted no longer includes the source script url in the comments section of the script. (http://www.sqlservercentral.com/scripts/Security/71562/)
Also, I had to add COLLATE DATABASE_DEFAULT to the map orphaned users section on one of my databases.
ON rm.name = sp.name COLLATE DATABASE_DEFAULT and rm.sid <> sp.sid
Howard
August 26, 2015 at 1:57 pm
Hello and thanks for the script. I need to modify the script so that it only returns results for a single login/user. Has anyone done this yet? I have to grant permissions to another login/user that is based on the permissions of an existing login/user. being able to do this would be very helpful as I have frequently get this type of request. sifting through the results for a single login/user is a little difficult on some of our larger systems.
thanks,
-d
Viewing 15 posts - 31 through 45 (of 58 total)
You must be logged in to reply to this topic. Login to reply