January 24, 2013 at 12:08 pm
AmarettoSlim (1/24/2013)
S. Kusen, thank you for this helpful script!
Glad you got some good use out of it!
Cheers.
July 24, 2013 at 12:25 pm
I had an older version of this script that I had inherited from my predecessor, stripped of any author information, and was just about to edit it so that it would script out schema permissions as well, but decided to ask google about it first. Lo and behold, I found 2.1, and saved myself a bit of work. The original script has already been more than useful, so I just wanted to say thanks!
January 24, 2014 at 9:29 am
I have to say that this script is great and it works amazing.
I have a suggestion about database permissions on SQL 2012. At our current location, we change database containment type to Partial. SQL logins are created at the database level as compared to server level. So when I script permissions out, the scripts will create SQL login without the password but cannot apply it since it cannot find the SQL login. Is there any option to add this?
Thanks!
January 24, 2014 at 11:17 am
Do you mean to say you need something like this? Try executing this piece (in text mode) on your database, it will generate create user scripts only for those users without a login.
SET NOCOUNT ON
DECLARE @name_holder VARCHAR(255)
DECLARE my_cursor CURSOR FOR
SELECT name
FROM sys.database_principals
WHERE Datalength(sid) >= 28
AND sid NOT IN (SELECT sid
FROM sys.server_principals)
AND type = 'S'
AND principal_id > 4
OPEN my_cursor
FETCH next FROM my_cursor INTO @name_holder
WHILE ( @@FETCH_STATUS <> -1 )
BEGIN
SELECT
'IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = '
+ Space(1) + '''' + name + '''' + ') BEGIN CREATE USER '
+ Space(1) + Quotename([name])
+ ' WITHOUT LOGIN WITH DEFAULT_SCHEMA = '
+ Quotename([default_schema_name]) + Space(1)
+ 'END; '
FROM sys.database_principals AS rm
WHERE [type] IN ( 'U', 'S', 'G' )
AND name = @name_holder
FETCH next FROM my_cursor INTO @name_holder
END
CLOSE my_cursor
DEALLOCATE my_cursor
January 24, 2014 at 1:35 pm
aruopna (1/24/2014)
Do you mean to say you need something like this? Try executing this piece (in text mode) on your database, it will generate create user scripts only for those users without a login.SET NOCOUNT ON
DECLARE @name_holder VARCHAR(255)
DECLARE my_cursor CURSOR FOR
SELECT name
FROM sys.database_principals
WHERE Datalength(sid) >= 28
AND sid NOT IN (SELECT sid
FROM sys.server_principals)
AND type = 'S'
AND principal_id > 4
OPEN my_cursor
FETCH next FROM my_cursor INTO @name_holder
WHILE ( @@FETCH_STATUS <> -1 )
BEGIN
SELECT
'IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = '
+ Space(1) + '''' + name + '''' + ') BEGIN CREATE USER '
+ Space(1) + Quotename([name])
+ ' WITHOUT LOGIN WITH DEFAULT_SCHEMA = '
+ Quotename([default_schema_name]) + Space(1)
+ 'END; '
FROM sys.database_principals AS rm
WHERE [type] IN ( 'U', 'S', 'G' )
AND name = @name_holder
FETCH next FROM my_cursor INTO @name_holder
END
CLOSE my_cursor
DEALLOCATE my_cursor
Thanks for this. I'll evaluate adding it to the script. I didn't adjust the script for contained DB users, obvioiusly. I'll see what I can do for getting an update out for that.
Appreciate you asking, Srizwanh, as well.
Steve
January 24, 2014 at 2:52 pm
In reviewing this for a while, I think the only way to go is to specify creating the user without a login, rather than setting a default password. I am unable to find a way to get at the DB user's password hash to script it out. In sp_help_revlogin, the LOGINPROPERTY function is used to get the PasswordHash property of the login, but obviously the contained DB users with passwords are not logins, and I cannot locate any DMV's or properties to grab that hash.
As such, if you have contained DB's, please see the below code to adjust. I will look at getting this update posted. To note, on the sys.database_principals view, the authentication_type of 2 is "DATABASE" in authentication types.
I believe this is the only fix to be adjusted as of now.
Thanks all for checking the script out.
SELECT '-- [-- DB USERS --] --' AS [-- SQL STATEMENTS --],
3 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT
CASE WHEN rm.authentication_type IN (2, 0) /* 2=contained database user with password, 0 =user without login; create users without logins*/ THEN ('IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = ' + SPACE(1) + '''' + [name] + '''' + ') BEGIN CREATE USER ' + SPACE(1) + QUOTENAME([name]) + ' WITHOUT LOGIN WITH DEFAULT_SCHEMA = ' + QUOTENAME([default_schema_name]) + SPACE(1) + 'END; ')
ELSE ('IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = ' + SPACE(1) + '''' + [name] + '''' + ') BEGIN CREATE USER ' + SPACE(1) + QUOTENAME([name]) + ' FOR LOGIN ' + QUOTENAME([name]) + ' WITH DEFAULT_SCHEMA = ' + QUOTENAME([default_schema_name]) + SPACE(1) + 'END; ')
END AS [-- SQL STATEMENTS --],
4 AS [-- RESULT ORDER HOLDER --]
FROM sys.database_principals AS rm
WHERE [type] IN ('U', 'S', 'G') -- windows users, sql users, windows groups
January 25, 2014 at 9:09 am
Hi Susan,
Thank you for looking into it. I will test it and get back to you with results.
July 25, 2014 at 7:06 am
SELECT 'IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = ' + SPACE(1) + '''' + [name] + '''' + ') BEGIN CREATE USER ' + SPACE(1) + QUOTENAME([name]) + ' FOR LOGIN ' + QUOTENAME([name]) + ' WITH DEFAULT_SCHEMA = ' + QUOTENAME([default_schema_name]) + SPACE(1) + 'END; ' AS [-- SQL STATEMENTS --],
The above part wroks fine if a Login and username have a same name, but in case if a user mapped to a login has a different name it script would does not make sense.
Can you please suggest how to handle the scenario where user name mapped to login are not of same name.
July 25, 2014 at 9:39 am
Hi virgo,
You can change the code over to the following that uses suser_sname(sid) instead of [name]. I'll update the posted script accordingly. Thanks for finding that issue.
+ ' FOR LOGIN ' + QUOTENAME([name]) +
+ ' FOR LOGIN ' + QUOTENAME(suser_sname([sid])) +
SELECT 'IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = ' + SPACE(1) + '''' + [name] + '''' + ') BEGIN CREATE USER ' + SPACE(1) + QUOTENAME([name]) + ' FOR LOGIN ' + QUOTENAME(suser_sname([sid])) + ' WITH DEFAULT_SCHEMA = ' + QUOTENAME([default_schema_name]) + SPACE(1) + 'END; ' AS [-- SQL STATEMENTS --],
4 AS [-- RESULT ORDER HOLDER --]
FROM sys.database_principals AS rm
WHERE [type] IN ('U', 'S', 'G') -- windows users, sql users, windows groups
I tested it out and seems to work for what you need.
Regards,
Steve
November 24, 2014 at 11:22 am
Thank you for creating/sharing this script! I'm a new DBA and I spend hours restoring from production to test and haven't been able to figure out how to script this. I've talked with a log of people and they have no idea. Thanks again.
Marcus
November 24, 2014 at 12:26 pm
mhorner 67968 (11/24/2014)
Thank you for creating/sharing this script! I'm a new DBA and I spend hours restoring from production to test and haven't been able to figure out how to script this. I've talked with a log of people and they have no idea. Thanks again.Marcus
You're welcome, glad you found it useful!
January 14, 2015 at 2:52 pm
Steve,
Script is very good and helps in copying db permissions specifically if you are overwriting db from different environment and with different db permissions.
Script output is very clean.
It fails to script any user defined DB roles and thus any users related to it.
Subhash
February 9, 2015 at 8:02 am
subhash.raut-762682 (1/14/2015)
Steve,Script is very good and helps in copying db permissions specifically if you are overwriting db from different environment and with different db permissions.
Script output is very clean.
It fails to script any user defined DB roles and thus any users related to it.
Subhash
Hi subhash.raut-762682,
I figured out what was missing. If you update lines 75 and 158 to have the ('G', 'S', 'U') updated to ('G', 'S', 'U', 'R'), then it should work.
The object-level permissions were working (ie grant select on dbo.table_1 to [your_database_role_name], but the permissions assigned to the database role at the database layer were being missed.
i.e. this permission would not be scripted out previously, but will be scripted out after changing those where clauses on the lines I specified:
GRANT CONNECT TO [your_database_role_name]
Thanks for pointing that out and helping me improve the script.
Cheers,
Steve
March 13, 2015 at 2:31 pm
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
March 27, 2015 at 2:17 pm
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....
Viewing 15 posts - 16 through 30 (of 58 total)
You must be logged in to reply to this topic. Login to reply