October 7, 2008 at 9:54 am
I can only script the logins or users but not the object level permissions associated with it. Can somebody help me generate the scripts?
October 7, 2008 at 10:07 am
BOL. fn_my_permissions
October 7, 2008 at 10:59 am
Permissions for all users in a database:
select U.name, O.name, permission_name from sys.database_permissions
join sys.sysusers U on grantee_principal_id = uid
join sys.sysobjects O on major_id = id
order by U.name
Permissions for a specific user in a database:
declare @username as varchar(50)
set @username = 'cans_application'
select O.name, permission_name from sys.database_permissions
join sys.sysusers U on grantee_principal_id = uid
join sys.sysobjects O on major_id = id
where U.name = @username
order by O.name
Greg
October 7, 2008 at 11:11 am
Hi Greg,
Thanks for the reply but I am sorry if my question was confusing. But I wanted to migrate the users and logins from SQL Server 2005 to another SQL server 2005 with the script method..So, all the object permissions associated with that logins or users should also be scripted. So, how do I do that?
Thanks,
October 7, 2008 at 1:24 pm
Use sp_help_revlogin from MS to transfer logins. I've used this script to script out database users and permissions http://www.sqlservercentral.com/scripts/Maintenance+and+Management/31959/.
Usually though, when I'm copying a database from one instance to another, I restore a backup on the new instance. That includes objects, users, and permissions.
Greg
October 7, 2008 at 2:00 pm
The script generator associates the permissions with the object scripts. So, when you are generating you script of the objects, you have to be sure to set the Include Permissions option to true.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 7, 2008 at 2:40 pm
If I only wanna script the object level permissions like the logins, database users, database roles associated to the user, object level permission(both for login and user) and the role members, can I do that?
I need this coz my company wants to script these things and put it in a folder.
can you please help
October 7, 2008 at 2:56 pm
It is not built-in to SQL Server to work that way. However, some folks have written procedures or programs that can do it. Hopefully someone here can provide a pointer to one of them.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 7, 2008 at 5:51 pm
Thanks guys I think I got a custom sql script written with cursor that provided me to some extent what I want.. I will work on it now.
October 7, 2008 at 6:25 pm
Here's an article from last year that may help: http://www.sqlservercentral.com/scripts/T-SQL+Aids/30754/
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 28, 2010 at 12:05 pm
I tried using Greg's script and it works perfectly for my needs on my production server, but when I attempt to run it on my test server, I'm getting a strange error. Both servers are running SQL Server 2005 sp2
Does anyone have any idea what might be causing this?
An error occurred while executing batch. Error message is: The directory name is invalid.
September 28, 2010 at 12:15 pm
Can you provide the script or link to it that you are using?
Are all the directories you have referenced in the script valid?
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
September 28, 2010 at 12:20 pm
This is the script. The username exists and has permissions. I can run this script on my local install of SSMS remotely connected to the test server. I can run the script on my production server. I just can't run it in SSMS on the test server itself. I'm not really referencing any directories. That's why this error message is so confusing.
declare @username as varchar(50)
set @username = 'cashnet'
select O.name, permission_name from sys.database_permissions
join sys.sysusers U on grantee_principal_id = uid
join sys.sysobjects O on major_id = id
where U.name = @username
order by O.name
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply