May 15, 2012 at 3:49 pm
Hi All,
I have an existing SQL server account and I would like to create a windows user with the same permissions, roles, default schema, etc.
Is anyone aware of a script that would do this for me?
Thanks
May 16, 2012 at 1:28 am
Is this in one particular DB or is it multiple DB's and server level rights?
The four main tables for DB permissions would be a join on these from within the database context where the login is the existing login you want to copy, if multiple DB's you could look at wrapping the statement in sp_msforeachdb, or joining with sys.databases and using QUOTENAME to build an xml string which executes in all database contexts.
select * from sys.database_principals
select * from sys.database_permissions
select * from sys.database_role_members
select * from sys.objects
For server level roles it would be a join on these tables
select * from sys.server_principals
select * from sys.server_permissions
select * from sys.server_role_members
May 17, 2012 at 7:51 am
Don't know if this will help, but see this link for a script I wrote for creating an instance audit documentation script. If you set the @outputtype variable = 2 (=1 just does nice column printing), it will create the actual assignment statements for all objects in the database (creates for all databases in instance). Just cut and past the permissions needed, in the section for the desired database, and do a replace for the new username.
I highly recommend, as an alternative, to create a database role, and give those permissions to the role, so that for any new users, you just give them that role, and the role actually contains all the permissions. Much easier.
You can use the sp_help_revlogin stored procedure in master (is on the web) to get the actual login script. Again, just change the username to the new one when running the new script.
Hope this helps.
http://www.sqlservercentral.com/Forums/Topic1226870-359-1.aspx#bm1230153
May 17, 2012 at 9:16 am
VikingDBA is right. This is why you use roles. If you have to do this once, you'll do it again.
Create a role, and use a script to get all permissions for the user and assign to the role. Then you can create a new login, and assign it to the role(s) in the database(s).
May 17, 2012 at 9:19 am
Thanks all. I'll pursue the role based approach.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply