Create a copy of Login1 (With all server and Database permissions)
-
I need to create a copy of a login on the same server. Lets say Login1 and clone the same permissions to Login2. Login2 will need all Server level and Database level permissions of Login1. The server is running on SQL Server 2005.
I need to clone atleast 1000 such users. What is the easiest approach for this. . .
Thanks in Advance !
Sanz -
You don't say if these are all Windows logins or if some/all are SQL logins.
If Windows, create a Windows security group and add them to that group. Then you only have to set up permissions once for the group.
If some/all are SQL logins you could create a role in the database(s) and just script the commands to add them to the role.
I have a script that will query a login and generate all server and database permissions for it, but I won't be back at my desk until Monday. I'm sure somebody else will be able to help before then.
-
All these logins are SQL logins. The script you referred to is exactly what I needed. (a script that will query a login and generate all server and database permissions for it).
Sanz -
use mydb
exec sp_helprotect
This should give u object level permissions on that DB for all users.
copy paste to excel and filter out to original user.
create a user role and give permissions as per the original user role
now you can start creating your logins and users and assign the role to each user.
Roles should help you in easy management of permissions.
-
I'm looking for a script to clone each login with server and database permissions.
Gathering object level permission(using sp_helpprotect) on each database is not practical in our case, as there are a very huge number of databases.
Sanz
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply