First thank you for this free script.
For User-Defined Table Types your script knows they are there but does not identify them Under the ObjectName. And of course it does not generate the GRANT and REVOKE statements.
Hopefully you can fix this in your code.
Thank you very much.
Thanks for letting me know. I don’t do much in the way of user defined objects so I didn’t notice. I’ll certainly get it added. If you would like to email me (email is in my about page) I’ll let you know when it’s finished.
Cool! Thank you and No rush. 🙂
If I may I would like to report another problem I have.
I am trying to delete a db user. I am running your script and copying all the REVOKE statements first.
This is the generated script I got:
REVOKE DELETE ON [sysrowsets] FROM [myDBUser1];
REVOKE EXECUTE ON [sysrowsets] FROM [myDBUser1];
REVOKE INSERT ON [sysrowsets] FROM [myDBUser1];
REVOKE SELECT ON [sysrowsets] FROM [myDBUser1];
REVOKE UPDATE ON [sysrowsets] FROM [myDBUser1];
When I run them I get this error five times:
Msg 15151, Level 16, State 1, Line 19
Cannot find the object ‘sysrowsets’, because it does not exist or you do not have permission.
I go to SSMS and script the “revoke” by unchecking some checked check boxes I do on the GUI, this is the generated script:
REVOKE DELETE ON SCHEMA::[mySchema1] TO [myDBUser1] AS [dbo]
REVOKE EXECUTE ON SCHEMA::[mySchema1] TO [myDBUser1] AS [dbo]
REVOKE INSERT ON SCHEMA::[mySchema1] TO [myDBUser1] AS [dbo]
REVOKE SELECT ON SCHEMA::[mySchema1] TO [myDBUser1] AS [dbo]
REVOKE UPDATE ON SCHEMA::[mySchema1] TO [myDBUser1] AS [dbo]
I guess the code is not generating the right schema name “mySchema1” and “FROM” I guess should be changed to “TO”.
Thanks again for your script!
Ok, I believe I have fixed the SCHEMA bug. Give it another shot. It isn’t something I can easily test since it requires a schema and an object to have the same ID but I’m fairly certain I have it.
Sorry I just returned to this page now. I updated for your new script and it now works for REVOKE statements.
Another bug would be when there is a schema other than the default ‘dbo’ on SPs (I presume perhaps other objects too) the GRANT and REVOKE statements you generate does not include the schema name, not even dbo.
Also the column SchemaName is NULL.
Ok, the user defined table types are now taken into account and the schemas have been added to object level GRANT and REVOKE statements. Let me know if you see anything else!
I haven’t thought this extensively but I believe the parts where you do IF @Print = 1 is wrong. Why do you need to have a different SQL string where you need to @Print or not ?
@Principal and @Role are defined as input parameters in the SP so you need their contents in the @SQL string.
In any case when I run your SP searching for a principal name, it does not work. The problem is IF @Print = 0, which is the default, the SQL will not put the values of @Principal.
Also maybe this is me not understanding but why is there a + here:
IF LEN(ISNULL(@Principal,@Role)) > 0
IF @Print = 1
SET @sql = @sql + NCHAR(13) + N’ AND DBPrincipals.name ‘ + @LikeOperator + N’ ‘ +
SET @sql = @sql + NCHAR(13) + N’ AND DBPrincipals.name ‘ + @LikeOperator + N’ ISNULL(@Principal,@Role) ‘
I’m surprised you are having a problem. I use it on an almost daily basis.
The main difference between the strings when @Print = 1 or not is that if it’s a 0 I’m constructing a string to run in dynamic SQL. If it’s a 1 then I’m constructing a string for that I can print and someone else can run the output. So for example if @print is 1 I have to include some variable declarations that I don’t need to do if I’m not going to print out it. If @print = 0 then I will pass the parameter values in using sp_executesql and I’ll actually put them in the string.
You are right the ISNULL(+QUOTENAME is a typo although it appears to work correctly. If you would like to email me at sqlstudent at gmail dot com and maybe send me some specifics about what you are trying to do I’d love to figure it out.
Sorry my mistake. I tried again yesterday and it seems that if the SP is not on the master DB, I had it created on a USER DB, then it doesn’t work. If it is created on the master DB, it works.
I do not know why exactly, I didn’t investigate.
Do you know why?
Probably has something to do with the database name. It might not be hitting the right database. Try specifying the @dbname parameter. In the mean time I’ll get it fixed 🙂
how is the best way to copy this script?
Unfortunately the plugin I’m using doesn’t have a copy button like some do. Typically I click in the top left corner of the script, scroll down and hold down the shift key while clicking on the bottom right of the script. If that’s still giving you issues you can email me and I’ll email you back with a link to an easier version to copy.
Does the 3rd result set include SP and function permission. I don’t see SP and function added to object level
Kindly ignore I found it
Is there a way to use sp_dbpermissions to save the results to tables?
The need is to save database users, their roles and explicitly granted permissions saved somewhere so they can be later restored elsewhere.
users_db_users would have all users
users_db_roles would have a row for each user and the role they are member of such as db_datareader.
users_db_objectpermissions would have a row for each explicitly granted permission such as GRANT SELECT on sometable to someuser
That’s a really good idea. I’ll add it to my next release (probably early next year). In the mean time you can use the @print option to print out the SQL and just add an insert into manually. Fair warning, while you can print out the code for the ‘ALL’ option it isn’t correct. I’ll try to get that fixed also.
Wow, you’ve put a lot of effort into these db and server permissions procedures; many thanks for sharing!
Thanks! Hope they are as useful to you as they are to me.
Hi again Kenneth,
I also noticed that when you don’t choose the ALL option, the USE DATABASE doesn’t show up in the output. So instead of having USE DATABASE; CREATE USER someuser you just get CREATE USER someuser. This causes problems when restoring if you have not selected the appropriate database before running the create and grant statements.
That’s actually deliberate. One of the purposes of the scripts is to be able to move them from one DB to another. So I assume that you will run them in the correct DB context. In the case of the ‘ALL’ option that doesn’t really apply since it’s across multiple databases.
I am planning to use this to generate scripts automatically prior to refreshing db. Is there a why to dump the necessary columns from the 3 results into a sql script so it can be used as a post refresh script ?
Unfortunately there is no easy way to do it. You can script out the queries used and modify them with the @print parameter but it doesn’t work well with the ALL parameter.
Hi again Kenneth. I used sp_dbpermissions to create a process that archives permissions for an instance to three tables in msdb. Then I created another process that can use those same tables to restore permissions as they were on any given date. I would definitely like to share the work with you if you’re interested.
Sure thing. Love to see it. Send it to my email address sqlstudent144 at gmail.
Hi Craig Efrein,
Can you share your restore permissions script with me? My email: WUYZ@hotmail dot com. Thanks.
Hi Craig, can you share your script. Thanks!
Could you please send me a copy? This would help us solve a big headache.
Send to email@example.com please and thank you!
I don’t have a restore process built in but there is now a parameter @DropTempTables that can be set to 0 to keep the existing temp tables. That way you can easily run the SP and save off the results right afterwards.
Is there a way to use this to list out information for a matching pattern of databases? like using wildcards?
No, currently it’s one DB or all of them. I do need to work on it though so I can add that to my list of future features.
This shows my ignorance … but I quickly tried running your script and did not get my expected results. I assumed that your script did not work for certificate and symmetric keys. But I did not see your USE MASTER at the top. Realize that you can pass in the DBName parameter, but that USE MASTER took me off guard as I was quickly googling and trying to find something that could handle my securables. Guess my suggestion is to remove the USE MASTER.
That just tells the the code where to put the SP. By putting sp_ at the beginning and putting it in master you can call it from any DB. Try this:
It doesn’t matter what DBName you use it will run for that DB without having to create it multiple times or use a 3 part name.
again … my ignorance. So the sproc with a prefix of ‘sp_’ will look in the current context first and if not found then in the master? Always wondered why sp_whoisactive could be run from any database.
Actually I think it’s the opposite. It checks master first then current context. But I could certainly be wrong. 🙂 It’s a common trick used. It’s also a good reason why you shouldn’t name regular SPs sp_.
Symmetric keys are showing NULL in the ObjectName, Revoke_statement, and grant_statement.. it should be
GRANT VIEW DEFINITION ON SYMMETRIC KEY::[symmetrickeyname] TO [login]
*nod* I probably have those excluded I’m afraid. Mostly due to my own ignorance. I don’t use keys enough to be certain of getting it right or even being able to create good test cases.
How did you exclude them? what part of the script would I look at?
Hmm, I looked and in that 3rd section I don’t have any exclusions so I don’t see why it’s not working. You could try setting @Print=1 and it will print out the queries it runs. Concentrate on the 3rd query and see what you can find. If you can find the bug I’ll certainly get it fixed and happily give you credit.
It is symmetric keys – plural. I assume it is also plural for asymmetric keys
N’ ”SYMMETRIC_KEYS” AS class_desc,’ + NCHAR(13) +
N’ ”SYMMETRIC KEYS” AS class ‘ + NCHAR(13) +
create database testdb
— create database specific dmk, symmetric key and certificate
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = ‘##MS_DatabaseMasterKey##’)
DECLARE @String varchar(128)
DECLARE @SQLCommand nvarchar(max)
SET @String = ‘MS_DatabaseMasterKey’
SET @SQLCommand = ‘
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ”’ + @String + ”’;’
EXECUTE sp_executesql @SQLCommand;
Print ‘ ##MS_DatabaseMasterKey## already exists’
IF NOT EXISTS (SELECT * FROM sys.certificates WHERE name = ‘Certificate1’)
CREATE CERTIFICATE Certificate1 WITH SUBJECT = ‘Protect Data’;
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = ‘SymmetricKey1’)
DECLARE @SQLCommand nvarchar(max)
DECLARE @String varchar(256)
DECLARE @String2 varchar(256)
SET @String = ‘SymmetricKey_Key_Source’
SET @String2 = ‘SymmetricKey_Identity_Value’
— Use the same Key_Source and Identity_Value on DBs that need to be able to encrypt/decrypt values
— on other databases.
SET @SQLCommand = ‘
CREATE SYMMETRIC KEY SymmetricKey1 with
KEY_SOURCE = ”’ + @String + ”’,
IDENTITY_VALUE = ”’ + @String2 + ”’,
ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE Certificate1;’
EXEC sp_executesql @SQLCommand
— map user to tempdb
IF NOT EXISTS (select * from sys.database_principals where name =’user1′)
create login user1 with password = ‘adofuoewruj3$’
cREATE USER user1 FOR LOGIN user1
EXEC sp_addrolemember ‘db_owner’, ‘user1’
GRANT VIEW DEFINITION ON SYMMETRIC KEY:: SymmetricKey1 TO user1
–REVOKE VIEW DEFINITION ON SYMMETRIC KEY:: SymmetricKey1 TO user1
GRANT CONTROL ON CERTIFICATE:: Certificate1 to user1
–REVOKE CONTROL ON CERTIFICATE:: Certificate1 to user1
— exec the sp_dbpermissions
EXEC dbo.sp_DBPermissions @Principal = ‘user1’, @UseLikeSearch= 0, @dbname = ‘testdb’
Fixed w credit! I feel better because it looks like it’s a bug in the documentation. If you look at BOL for sys.database_permissions you’ll see them listed without the Ss.
I assumed that it would be asymmetric_keys, but it is not – as you already must know.
CREATE ASYMMETRIC KEY asymmetrickey1
WITH ALGORITHM = RSA_2048
ENCRYPTION BY PASSWORD = ‘a))9sdfkhhYYY^%%asdf32asdf$’;
GRANT VIEW DEFINITION ON aSYMMETRIC KEY:: asymmetrickey1 TO user1
EXEC dbo.sp_DBPermissions @Principal = ‘user1’, @UseLikeSearch= 0, @dbname = ‘testdb’
Yep, just the one is wrong in the docs. Just to make it extra confusing 🙂
ok, cannot remember exactly if I used the @Print=1 or added a SELECT (because the PRINT output was truncating), but I took some of the printed code and mashed it into a script. Well the CHAR(13) with out the CHAR(10) causes the SQL debugger fits. The debugger would not step past the CTE ObjectList. Spent a lot of time trying to figure out what was up with the debugger (I am a debugger manac). Finally took the script and replaced all single CHAR(13) with CHAR(13) + CHAR(10) and the debugger started working correctly. Just an FYI.
I really need to do a bunch of work on it so I’ll add that to the list. Maybe make an @lf var so people can modify it as they need.
Sorry to be a pest. The symmetric keys is not exactly right. Not both are plural. the REVOKE and GRANT should not have plural.
N’ SELECT NULL AS SchemaName, ‘ + NCHAR(13) + NCHAR(10) +
N’ name ‘ + @Collation + N’ AS name, ‘ + NCHAR(13) + NCHAR(10) +
N’ symmetric_key_id AS id, ‘ + NCHAR(13) + NCHAR(10) +
N’ ”SYMMETRIC_KEYS” AS class_desc,’ + NCHAR(13) + NCHAR(10) +
N’ ”SYMMETRIC KEY” AS class ‘ + NCHAR(13) + NCHAR(10) +
N’ FROM sys.symmetric_keys’ + NCHAR(13) + NCHAR(10) +
Not at all. I appreciate the help. I’ll get it taken care of tonight 🙂
Sorry for the delay. It’ fixed now. Thanks again for your help with these things 🙂
It’s not showing up object level permissions on the Views of the database. Am i missing something?
I take it back! I have a long list which I pasted in excel. Missed a filter. It’s all good 🙂
Glad to hear it! Hope you are finding the whole thing helpful.
Found one minor issue; when setting @Print = 1, the third (database & Object permissions) section’s query gets truncated. This can be resolved by commenting out line 739 and inserting the following two lines between lines 740 and 741.
PRINT CAST(@use AS nvarchar(max))
PRINT CAST(@ObjectList AS nvarchar(max))
Hope it helps,
Changed. And thanks!
do you have an email address? I have a version of your script with column permissions added that I would like to send for your review.
Minor inconsistency found between your documentation and the actual script:
What type of output is desired.
Default – Either ‘Default’ or it doesn’t match any of the allowed values then the SP
will return the standard 3 outputs.
None – No output at all. Usually used if you keeping the temp tables to do your own
CreateOnly – Only return the create scripts where they aren’t NULL.
DropOnly – Only return the drop scripts where they aren’t NULL.
ScriptsOnly – Return drop and create scripts where they aren’t NULL.
Report – Returns one output with one row per principal and a comma delimited list of
roles the principal is a member of and a comma delimited list of the
individual permissions they have.
Script is actually looking for ‘ScriptOnly’ and not ‘ScriptsOnly’.
Thanks for the catch! Fixed here and on sp_SrvPermissions (note: the documentation was the correct version. ‘ScriptsOnly’)
Could you please email this script to me? i am getting a fatal script error occurred incorrect syntax encountered while parsing go
Send me a quick email with your address (assuming you don’t want to put it here). Mine is in my about/contact me 🙂
Just to let you know I am still using this script and thank you for maintaining it.
Glad to hear it!
Just wanted to know if we can pass multiple login names in the LoginName parameter?
I want the script of permissions of multiple logins at the same time. How can we do that?
Unfortuntely no, it doesn’t have a way to pass in a list of login names. However you can do things like include only certain login types (SQL Ids, AD Groups etc) and by default the name is handled as a pattern match so if you send in ‘BOB’ it will pull any login name LIKE ‘%BOB%’. If none of that works for you I’d suggest scripting each Id out separately and merging the scripts.
Can you please email the script to me, i tried downloading but I am not able to download it
If you look there is a link to the github repository near the top of the comment block that should make it easier. If it doesn’t feel free to give me your email address and I’ll send it to you.
Thank you so much for sharing this with the community
Fill in your details below or click an icon to log in:
You are commenting using your WordPress.com account.
( Log Out /
You are commenting using your Google account.
( Log Out /
You are commenting using your Twitter account.
( Log Out /
You are commenting using your Facebook account.
( Log Out /
Connecting to %s
Notify me of new comments via email.
Notify me of new posts via email.
This site uses Akismet to reduce spam. Learn how your comment data is processed.
RSS - Posts
RSS - Comments
Enter your email address to follow this blog and receive notifications of new posts by email.
Join 3,142 other followers