MS SQL 2008 Export Permission/Securable Into Either Excel 2007 or Visio 2007 (not Pro Versions)

We need the complete list of permission/securable of all users for all our databases and server.

Via 'Microsoft SQL Server Management Studio' I've looked manually, by right clicking on the database and write down via 'Permissions' Tab the permissions of all databases.

Sadly these are not all permissions, for example tables/views securables are not listed.

Is there a way to export ALL permissions into Excel/Visio 2007? (It doesn't have to be all at once.)



Hi,

you can do that with SSMS. Go to "Tools" - "Options" and then in the settings of "SQL Server Object Explorer" - "Scripting", then on the right side under "Object scripting options" the setting "Script permissions" and set it to True.

Then click on i.e. the "Tables" folder and choose the menu "View" - "Object Explorer Details". You see the list of all tables. Mark them all by either CTRL-A or by holding the SHIFT key and clicking the first and the last, then right click one of the marked tables and use "Script - Create To New Query Window". That will script all objects together with the Grant/Deny commands.

You can repeat the same with other folders, i.e. the Schemas folder under Security folder as normally you would not set single permissions to tables but general permissions to schemas or views assigned to roles so that groups of users can be assigned to roles, otherwise it would be horrible to administer the permissions.

If you would like to have an overview table you would then need to create a script which parses this text and create a table from it.

Cheers,

Christian



Hi,

you can do that with SSMS. Go to "Tools" - "Options" and then in the settings of "SQL Server Object Explorer" - "Scripting", then on the right side under "Object scripting options" the setting "Script permissions" and set it to True.

Then click on i.e. the "Tables" folder and choose the menu "View" - "Object Explorer Details". You see the list of all tables. Mark them all by either CTRL-A or by holding the SHIFT key and clicking the first and the last, then right click one of the marked tables and use "Script - Create To New Query Window". That will script all objects together with the Grant/Deny commands.

You can repeat the same with other folders, i.e. the Schemas folder under Security folder as normally you would not set single permissions to tables but general permissions to schemas or views assigned to roles so that groups of users can be assigned to roles, otherwise it would be horrible to administer the permissions.

If you would like to have an overview table you would then need to create a script which parses this text and create a table from it.

Cheers,

Christian



@Bitsqueezer
This is an awesome way to get the tables and views.

However, the problem remains that I do not get this way the special permissions/securables of users/groups that way or can you provide a way too via the scripts you have mentioned how to export them as well or some other way?

Secureables I can currently only see by going to:ServerDatabases'Database Name'SecurityUsers'User Name'
Right click on the 'User Name'PropertiesSecurables
And only then I see which rights the user/group has on each view (not sure there is as well securables for tables).



Hi,

maybe the script from this link can help you:http://stackoverflow.com/questions/1500290/how-to-script-automatically-the-securables-assigned-to-a-sql-account
(coming from http://blogs.msdn.com/b/blogdoezequiel/archive/2010/04/26/the-sql-swiss-army-knife-1.aspx)

You only need to add this at the end of the script before the temp tables are dropped:



Will test that most likely on Monday.



No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:Accept: Bitsqueezer (http:#40677832)

If you feel this question should be closed differently, post an objection and a moderator will read all objections and then close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

MartinLiss
Experts-Exchange Cleanup Volunteer

Share this

Related Posts

There was an error in this gadget