Duplicating Logins - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Duplicating Logins

Author

Topic

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-04-05 : 09:09:45

Hi all

I've got a user with permissions set up for various databases on a server.
What I want to do is duplicate those permissions for another user (a new starter) without having to go through database and assign permissions manually.
Both logins will be on the same server.

Two qustions:-
1) Is this possible?
2) If so, how is it done?

At some point I'd like to have this as a stored procedure where I can pass parameters (e.g. new starter details and login to copy) but for now I'll cope with the script if it exists.

All help gratefully received.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-05 : 10:35:59

why not add a role with defined set of permissions and map users to it. this will avoid setting up set of permissions individually for each user

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-04-05 : 11:39:27

From what I've seen on the net (courtesy of google), I can't add new groups to the server. If that's incorrect, can you let me know how that's done please?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-05 : 12:07:51

nope. you can create a role with define set of permission using CREATE ROLE statement

http://msdn.microsoft.com/en-us/library/ms187936(v=sql.100).aspx

then use sp_addrolemember to add members to role

http://msdn.microsoft.com/en-us/library/ms187750(v=sql.100).aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-04-06 : 07:11:42

I need to do this at server level not at database level.
I need to set permissions on 8 different databases at the same time.

I don't think this is possible in SQL Server 2008 R2.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-06 : 09:06:40

then use CREATE SERVER ROLE if you're on sql 2012 or above

http://msdn.microsoft.com/en-us/library/ee677610.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-04-06 : 11:53:40

I'm on 2008 R2 so I don't know I'd that will work which is why I wanted to copy the logins directly

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-06 : 15:14:08

nope...it wont work in sql 2008

if you want copy logins use transfer database objects task

http://msdn.microsoft.com/en-us/library/ms142159(v=sql.100).aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-04-09 : 05:30:01

Any idea which part?
I've checked this page:
http://msdn.microsoft.com/en-us/library/ms142159%28v=sql.105%29.aspx

but I'm not sure what logins would come under.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-09 : 11:06:18

see this

http://msdn.microsoft.com/en-us/library/ms187693(v=sql.105).aspx

this has full list of options which includes login too

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-04-11 : 02:42:10

I'm now attacking this from a different direction.

Is it possible to use msdb.dbo.sp_addrolemember with a specific database and role?
I've used it before to add a member to DatabaseMailUserRole in the MSDB database, but wondered if it was possible to specify the database and role in one go?

My code for adding a member to that role is:-


	exec msdb.dbo.sp_addrolemember @rolename='DatabaseMailUserRole'
	,@membername=@username

where @username is passed as a parameter.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-12 : 00:36:16

you mean add the role to multiple dbs in one go?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-04-12 : 02:11:32

Basically, yes.
In some semblance of order, what I want to do is:-
1) Create the user where necessary (I don't see that being a problem, I've got a stored procedure I can call for that)
2) Add the user to a small list of database with the relevant permissions (db_datareader,db_datawriter,db_owner are the most common)

I need to do this for 9 databases.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources