66 thoughts on “sp_DBPermissions”

1. anonymous says:

   February 5, 2014 at 11:48 AM

   Hello,
   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.

   Reply
   • Kenneth Fisher says:

     February 5, 2014 at 12:12 PM

     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.

     Reply
     • anonymous says:

       February 5, 2014 at 3:37 PM

       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:

       use [MyDB]
       GO
       REVOKE DELETE ON SCHEMA::[mySchema1] TO [myDBUser1] AS [dbo]
       GO
       use [MyDB]
       GO
       REVOKE EXECUTE ON SCHEMA::[mySchema1] TO [myDBUser1] AS [dbo]
       GO
       use [MyDB]
       GO
       REVOKE INSERT ON SCHEMA::[mySchema1] TO [myDBUser1] AS [dbo]
       GO
       use [MyDB]
       GO
       REVOKE SELECT ON SCHEMA::[mySchema1] TO [myDBUser1] AS [dbo]
       GO
       use [MyDB]
       GO
       REVOKE UPDATE ON SCHEMA::[mySchema1] TO [myDBUser1] AS [dbo]
       GO

       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!

       Reply
       • Kenneth Fisher says:

         February 7, 2014 at 4:45 PM

         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.

     • Anonymous says:

       February 12, 2014 at 4:26 PM

       Hello,

       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.

       Thank you!

       Reply
       • Kenneth Fisher says:

         February 15, 2014 at 11:43 AM

         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!

2. Anonymous says:

   February 26, 2015 at 7:05 PM

   Hello,

   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:
   ISNULL(+QUOTENAME

   IF LEN(ISNULL(@Principal,@Role)) > 0
   IF @Print = 1
   SET @sql = @sql + NCHAR(13) + N’ AND DBPrincipals.name ‘ + @LikeOperator + N’ ‘ +
   ISNULL(+QUOTENAME(@Principal,N””),QUOTENAME(@Role,””))
   ELSE
   SET @sql = @sql + NCHAR(13) + N’ AND DBPrincipals.name ‘ + @LikeOperator + N’ ISNULL(@Principal,@Role) ‘

   Thanks!

   Reply
   • Kenneth Fisher says:

     February 26, 2015 at 7:53 PM

     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.

     Reply
     • Anonymous says:

       March 3, 2015 at 6:49 PM

       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?

       Thanks!

       Reply
       • Kenneth Fisher says:

         March 3, 2015 at 10:23 PM

         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 🙂

3. brenda grossnickle says:

   June 17, 2015 at 4:31 PM

   how is the best way to copy this script?

   Reply
   • Kenneth Fisher says:

     June 17, 2015 at 4:32 PM

     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.

     Reply
4. H. says:

   October 16, 2015 at 3:01 AM

   Does the 3rd result set include SP and function permission. I don’t see SP and function added to object level

   Reply
   • H. says:

     October 16, 2015 at 3:26 AM

     Kindly ignore I found it

     Reply
5. cjefrein says:

   October 26, 2015 at 8:21 AM

   Hello,

   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

   Reply
   • Kenneth Fisher says:

     October 26, 2015 at 8:44 AM

     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.

     Reply
6. Steve Armistead says:

   October 27, 2015 at 10:19 AM

   Wow, you’ve put a lot of effort into these db and server permissions procedures; many thanks for sharing!

   Reply
   • Kenneth Fisher says:

     October 27, 2015 at 5:49 PM

     Thanks! Hope they are as useful to you as they are to me.

     Reply
7. cjefrein says:

   November 6, 2015 at 8:22 AM

   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.

   Sincerely,
   Craig

   Reply
   • Kenneth Fisher says:

     November 6, 2015 at 9:17 AM

     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.

     Reply
8. RD says:

   April 23, 2016 at 1:06 PM

   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 ?

   Reply
   • Kenneth Fisher says:

     April 23, 2016 at 4:23 PM

     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.

     Reply
9. Craig Efrein says:

   April 28, 2016 at 1:47 AM

   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.

   Reply
   • Kenneth Fisher says:

     April 28, 2016 at 7:31 AM

     Sure thing. Love to see it. Send it to my email address sqlstudent144 at gmail.

     Reply
   • YingzhengWu says:

     November 15, 2016 at 1:19 AM

     Hi Craig Efrein,
     Can you share your restore permissions script with me? My email: WUYZ@hotmail dot com. Thanks.

     Reply
   • Rox says:

     February 14, 2017 at 12:20 PM

     Hi Craig, can you share your script. Thanks!
     roxan.meza@gmail.com

     Reply
   • Jo Rodriguez says:

     November 28, 2017 at 2:44 PM

     Could you please send me a copy? This would help us solve a big headache.

     Reply
     • Jo Rodriguez says:

       November 28, 2017 at 2:45 PM

       Send to jrodriguez@cppib.com please and thank you!

       Reply
       • Kenneth Fisher says:

         November 28, 2017 at 3:55 PM

         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.

10. Jeremy Bennett says:

    May 25, 2016 at 8:11 AM

    Is there a way to use this to list out information for a matching pattern of databases? like using wildcards?

    Reply
    • Kenneth Fisher says:

      May 25, 2016 at 8:19 AM

      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.

      Reply
11. brenda grossnickle says:

    November 29, 2016 at 11:17 AM

    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.

    Reply
    • Kenneth Fisher says:

      November 29, 2016 at 11:38 AM

      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:

      USE DBName
      EXEC sp_DBPermissions

      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.

      Reply
      • brenda grossnickle says:

        November 29, 2016 at 11:50 AM

        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.

        Reply
        • Kenneth Fisher says:

          November 29, 2016 at 11:56 AM

          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_.

12. brenda grossnickle says:

    November 29, 2016 at 11:44 AM

    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]

    Reply
    • Kenneth Fisher says:

      November 29, 2016 at 11:57 AM

      *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.

      Reply
      • brenda grossnickle says:

        November 29, 2016 at 12:21 PM

        How did you exclude them? what part of the script would I look at?

        Reply
        • Kenneth Fisher says:

          November 29, 2016 at 12:30 PM

          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.

        • brenda grossnickle says:

          November 29, 2016 at 1:25 PM

          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
          go

          use testdb
          go

          — create database specific dmk, symmetric key and certificate
          IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = ‘##MS_DatabaseMasterKey##’)
          BEGIN
          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;
          END
          ELSE
          BEGIN
          Print ‘ ##MS_DatabaseMasterKey## already exists’
          END

          IF NOT EXISTS (SELECT * FROM sys.certificates WHERE name = ‘Certificate1’)
          CREATE CERTIFICATE Certificate1 WITH SUBJECT = ‘Protect Data’;
          GO

          IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = ‘SymmetricKey1’)
          BEGIN
          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
          END

          GO

          — map user to tempdb
          IF NOT EXISTS (select * from sys.database_principals where name =’user1′)
          begin
          create login user1 with password = ‘adofuoewruj3$’
          cREATE USER user1 FOR LOGIN user1
          end

          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’

        • Kenneth Fisher says:

          November 29, 2016 at 2:36 PM

          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.

13. brenda grossnickle says:

    November 29, 2016 at 3:07 PM

    I assumed that it would be asymmetric_keys, but it is not – as you already must know.

    use testdb

    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’

    Reply
    • Kenneth Fisher says:

      November 29, 2016 at 3:09 PM

      Yep, just the one is wrong in the docs. Just to make it extra confusing 🙂

      Reply
14. brenda grossnickle says:

    December 1, 2016 at 4:05 PM

    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.

    Reply
    • Kenneth Fisher says:

      December 1, 2016 at 4:29 PM

      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.

      Reply
15. brenda grossnickle says:

    December 2, 2016 at 3:19 PM

    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) +

    Reply
    • Kenneth Fisher says:

      December 2, 2016 at 5:01 PM

      Not at all. I appreciate the help. I’ll get it taken care of tonight 🙂

      Reply
      • Kenneth Fisher says:

        December 7, 2016 at 8:38 AM

        Sorry for the delay. It’ fixed now. Thanks again for your help with these things 🙂

        Reply
16. Ramya says:

    January 5, 2017 at 2:38 PM

    It’s not showing up object level permissions on the Views of the database. Am i missing something?

    Reply
    • Ramya says:

      January 5, 2017 at 3:01 PM

      I take it back! I have a long list which I pasted in excel. Missed a filter. It’s all good 🙂

      Reply
      • Kenneth Fisher says:

        January 5, 2017 at 3:07 PM

        Glad to hear it! Hope you are finding the whole thing helpful.

        Reply
17. Chad Estes says:

    January 13, 2017 at 8:45 AM

    Kenneth,
    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,
    Chad

    Reply
    • Kenneth Fisher says:

      January 13, 2017 at 10:15 AM

      Changed. And thanks!

      Reply
      • Ricky Lively says:

        May 11, 2017 at 8:13 PM

        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.

        Reply
        • Kenneth Fisher says:

          May 11, 2017 at 8:29 PM

          sqlstudent144@gmail.com

18. Chad Estes says:

    December 12, 2017 at 10:25 AM

    Minor inconsistency found between your documentation and the actual script:

    @Output
    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
    reporting.
    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’.

    Reply
    • Kenneth Fisher says:

      December 14, 2017 at 7:20 AM

      Thanks for the catch! Fixed here and on sp_SrvPermissions (note: the documentation was the correct version. ‘ScriptsOnly’)

      Reply
19. sunny patel says:

    March 26, 2018 at 9:36 AM

    Could you please email this script to me? i am getting a fatal script error occurred incorrect syntax encountered while parsing go

    Reply
    • Kenneth Fisher says:

      March 26, 2018 at 9:59 AM

      Send me a quick email with your address (assuming you don’t want to put it here). Mine is in my about/contact me 🙂

      Reply
20. Zyvox says:

    March 21, 2019 at 5:43 AM

    Hello,

    Just to let you know I am still using this script and thank you for maintaining it.

    Reply
    • Kenneth Fisher says:

      March 21, 2019 at 6:45 AM

      Glad to hear it!

      Reply
21. Sachin Jain says:

    July 17, 2019 at 12:51 AM

    Hi Kenenth,

    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?

    Reply
    • Kenneth Fisher says:

      July 17, 2019 at 7:21 AM

      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.

      Reply
22. madhav says:

    December 23, 2019 at 2:57 PM

    Can you please email the script to me, i tried downloading but I am not able to download it

    Reply
    • Kenneth Fisher says:

      December 23, 2019 at 3:13 PM

      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.

      Reply
23. Ken Grissom says:

    July 18, 2020 at 7:28 PM

    Thank you so much for sharing this with the community

    Reply

Leave a Reply to Chad Estes Cancel reply

Enter your comment here...

Fill in your details below or click an icon to log in:

Email (required) (Address never made public)

Name (required)

Website

You are commenting using your WordPress.com account. ( Log Out /  Change )

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

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.