The SQL Swiss Army Knife #1 – Scripting Securables – Updated

EDIT (11-01-2013):  Fixed issue with generating all logins even when single database was chosen.
EDIT (17-05-2015): Fixed duplicate permissions scripted with Procedures/Functions/CLR; Extended object coverage.
EDIT (11/18/2016): Fixed issue with permissions being repeated.

Download here: usp_SecurCreation.sql

Hello all,

This is the first in a new series sharing SQL scripts that may help on everyday DBA tasks, something in the likes of a "SQL Swiss Army Knife".
According to BOL, SQL securables "are the resources to which the SQL Server Database Engine authorization system regulates access".

I've been using the following script as a "local machine" bit of a wider DR strategy for years, because you never know when you need to quickly review or restore permissions over scopes when they got "accidently" changed or deleted.
Its output may simply be saved onto a .sql file and will resemble this:


So for my first post, here is the script which I hope some of you will find useful.

These are the options available:

  • All users:
    EXEC usp_SecurCreation
  • One user, All DBs:
    EXEC usp_SecurCreation '<User>'
  • One user, One DB:
    EXEC usp_SecurCreation '<User>', '<DBName>'
  • All users, One DB:
    EXEC usp_SecurCreation NULL, '<DBName>'

Until next time!

Disclaimer: I hope that the information on these pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. All information on these pages is provided "as -is", without any warranty, whether express or implied, of its accuracy, completeness, fitness for a particular purpose, title or non-infringement, and none of the third-party products or information mentioned in the work are authored, recommended, supported or guaranteed by Ezequiel. Further, Ezequiel shall not be liable for any damages you may sustain by using this information, whether direct, indirect, special, incidental or consequential, even if it has been advised of the possibility of such damages.

Comments (9)
  1. Ignacio Quijas says:

    Hi Pedro,

    I ran the script but it wrongly assigns EXECUTE permissions to views defined with the Select attribute. It processes views as if they were stored procedures :-O


  2. pmasl says:

    Thanks Ignacio,

    It's fixed now. Please try it and give some more feedback.



  3. Franjo Stipanovic fritzfs says:

    I've used it today. Thanks! 🙂

  4. srk says:

    awesome…can we also get permission at table level ?

  5. Just Poland Guy says:

    You just saved few hours of my life!


  6. Ajith Bhojani says:

    Hi thanks for the script. Unfortunately it did not script the views permissions.

  7. Misci says:

    Thumb up!

    1. Ben Sugden says:

      Thank you, this was really useful. I’m migrating from a SQL 2008 production server to SQL 2017 and this saved me a huge chunk of time.

      I did have to tweak the script somewhat though, the user/login I migrated did not appear in the [column_privileges] or [table_privileges] as ‘[MyUser]’, instead it appeared as ‘MyUser’.

      This resulted in Table, View and Column privileges being excluded from my User based query.

      I stripped the square brackets from the areas of the script that queried these two tables and everything was fine. Thanks again!

Comments are closed.

Skip to main content