sp_DBPermissions

63

63 thoughts on “sp_DBPermissions

  1. anonymous says:

    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.

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

      • anonymous says:

        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!

        • 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:

        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!

        • 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:

    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!

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

      • Anonymous says:

        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!

        • 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:

    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.

  4. H. says:

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

  5. cjefrein says:

    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

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

  6. Steve Armistead says:

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

  7. cjefrein says:

    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

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

  8. RD says:

    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 ?

  9. Craig Efrein says:

    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.

  10. Jeremy Bennett says:

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

  11. brenda grossnickle says:

    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:

      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.

      • brenda grossnickle says:

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

  12. brenda grossnickle says:

    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.

      • brenda grossnickle says:

        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.

        • brenda grossnickle says:

          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’

        • 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:

    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’

  14. brenda grossnickle says:

    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.

  15. brenda grossnickle says:

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

  16. Ramya says:

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

  17. Chad Estes says:

    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

  18. Chad Estes says:

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

  19. sunny patel says:

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

  20. Zyvox says:

    Hello,

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

  21. Sachin Jain says:

    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?

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

Leave a Reply to Ramya Cancel reply

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

WordPress.com Logo

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

Google photo

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

Twitter picture

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

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,087 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: