April 1, 2008 at 4:29 pm
SQL Server 2005 is a big improvement over 2000 in many ways but one area it seems to be lacking is with automatic scripting of various objects. I've seen posts about the system information views (new as of 2005) as well as some posts with code on how to script permissions (via Stored Procedure) for specific types of objects like DB Roles but I've yet to find a way to script permissions for SQL Objects in general.
In Ent Mgr for SQL 2000 you could right-click on just about anything and get T-SQL code auto generated for the object. While SSMS 2005 has this to some extent, there doesn't seem to be any way to script permissions for the object. Does anyone know if there is a method or process in 2005, be it a view, SP, UDF or anything else that will generate T-SQL for object permissions for any type of object and not just one like DB Roles?
In our environment we have several DB's (been around for a whele) that have numerous object specific permissions and it would be great to find a way to generate T-SQL for these.
Thanks
Kindest Regards,
Just say No to Facebook!April 1, 2008 at 7:35 pm
William,
There are a lot more options in 2005 than you believe. You can click tools --> Options --> Scripting and set a whole slew of options. Among these options is permissions.
As a side note on creating scripts, you can open your object listing in Object Explorer Details and select more than one object and create scripts. This will create a single script with all the selected objects.
You will also note that you can right-click a database --> tasks --> generate scripts.
Here you can script any or all objects, permissions, keys etc...
April 2, 2008 at 12:53 pm
Adam,
Thanks! Thats exactly what I was looking for. We are new to switching over to 2005 and I did not realize there were scripting options nor did I notice the Generate Scritps menu item under the Database node. I was selecting specific roles/users and selecting to script them and was not finding a way to script their permissions.
Thanks Again!
Kindest Regards,
Just say No to Facebook!April 11, 2008 at 6:21 pm
Adadm -
It looks like I spoke to soon with my last reply. The Generate Scripts feature is not cutting it. You can get it to generate permissions but only if you have included in the scripts output the object the permissions are applied tfor and not just aplied to.
I'm trying to find a way to easily get the T-SQL needed to recreate all Login/User specific permisssions In a database. So for example in our production DB there are 2 SQL Logins setup as DB Users with object specific permissions. In order to get the T-SQL to scrip those permissions I have to select each object during the Generate Script process and that means I have to know in avdance every object there is a specific permission for.
Isn't there some way, even if it's not a generated script, to get an answer to the following:
Can you show me all object specific permissions associated with 1 or more DB Users?
I've played around some with the fn_my_permissions UDF and the [/b\fn_builtin_permissions[/b] UDF and I have to say that they don't cut the mustard for really telling you what has pemmission to what in the DB only general permissions.
Thanks
Kindest Regards,
Just say No to Facebook!April 11, 2008 at 7:13 pm
I wrote an open source app called scriptdb that will script out all objects in a db, and optionally the permissions on them.
It would be a fairly simple matter to modify it to leave out the script for the object, and only script the permission. It's implemented in C# and uses SMO.
http://www.codeplex.com/scriptdb
---------------------------------------
elsasoft.org
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply