7

Over the years, I have used countless scripts and applications to generate all permissions for SQL Server databases. I have used both paid and free tools. I feel that not one of them seems to be accurate in every instance. I am simply looking for a way to generate all permissions for a given database along with confidence that the script is correct and able to be used after a backup/restore from another instance. Does anyone have a method they have confidence in and use regularly

5

I have a couple of stored procedures that I wrote and use on a regular basis.

They each have three outputs.

  • Principals : A list of the database/server principals and associated info including create/drop scripts.
  • Role membership : A list of what roles each database/server principal belongs to along with add/drop scripts.
  • Permissions : A list of individual permissions (EXECUTE, INSERT, UPDATE, CONNECT etc) for each database/server principal.

Obviously the Srv sp is for the Server principals and the DB sp is for the database level principals. The DB sp also has an 'All' option for the database name. With this option it runs through and pulls all permissions for all databases.

Note: This was originally intended as a research tool so there are a fair number of options that will let you parse down your data. Just roles, everything but roles, just a specific principal name etc.

  • 1
    Great couple of scripts! I use them a lot too, really handy pair of sp's. And been able to transfer password's and SID's, thumbs up for you Kenneth – Yaroslav Apr 4 '16 at 19:08
  • 1
    Good scripts Kenneth! sp_DBPermissions seems to return everything I need with the exception of "VIEW DEFINITION" that is granted to users on a database. No records are returned for those. – SomeGuy Apr 28 '16 at 14:28
  • Odd. That should show up. I would a) confirm that under the database they are explicitly granted VIEW DEFINITION (not through a role, AD group etc) and then double check the system views. sys.database_permissions joined to sys.database_principals. If it's not in those views it won't show up in the SPs. – Kenneth Fisher Apr 28 '16 at 14:58
2

sp_help_revlogin

See: How to transfer logins and passwords between instances of SQL Server on MSDN

Worked reasonably well for me this past summer.

  • sp_help_revlogin only transfers server principal information. It doesn't transfer anything at a database level. Very useful for what it does though. – Kenneth Fisher Apr 4 '16 at 18:49
  • if you move/migrate to a new server you should not need to script every db permission over. They will come along with the migrated db. – Michael P Apr 4 '16 at 19:26
  • Very true. Unless you happen to be migrating between SDLC levels. Say copying over dev with test but then you want to put the dev permissions back the way they were. – Kenneth Fisher Apr 4 '16 at 19:44
  • @KennethFisher you usually have roles for that. – spaghettidba Apr 5 '16 at 8:19
  • @spaghettidba Don't the permissions for the roles change between dev & test? Or prod & test? Or at least the members? It's handy to have that type of thing scripted out. – Kenneth Fisher Apr 5 '16 at 13:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.