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.)
HelpdeskJBCAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BitsqueezerCommented:
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
0
HelpdeskJBCAuthor Commented:
@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:
Server\Databases\'Database Name'\Security\Users\'User Name'
Right click on the 'User Name'\Properties\Securables\
And only then I see which rights the user/group has on each view (not sure there is as well securables for tables).
0
BitsqueezerCommented:
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:
SELECT State, State2, PermName, Type, Grantor, [User] FROM #TempSecurables
SELECT DBName, State FROM #TempSecurables2

Open in new window


Cheers,

Christian
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HelpdeskJBCAuthor Commented:
Will test that most likely on Monday.
0
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.