SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Alter autorization on database for multiple login
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gsmccoy
Starting Member

USA
7 Posts

Posted - 09/25/2012 :  10:00:13  Show Profile  Reply with Quote
I am in the middle of a large conversion from two slow physical servers into a screaming fast single server. During this process I have to map multiple databases to various user accounts. I can use the below syntax to perform this function one user at a time:

alter authorization on database::CTGCUSTOM to ALESTETMW ;

However I cannot figure out how to do this for multiple users. I have over 800 users I need to map and having multiple lines like below does not work

alter authorization on database::CTGCUSTOM to ALESTETMW ;
alter authorization on database::CTGCUSTOM to ALLPAUTMW ;
alter authorization on database::CTGCUSTOM to ALNSECTMW ;
alter authorization on database::CTGCUSTOM to ALSJERTMW ;
alter authorization on database::CTGCUSTOM to ANDDOUTMW ;

Can anyone show me how to do this for multiple users?

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/25/2012 :  10:50:45  Show Profile  Reply with Quote
I must admit that I am a little confused by your example. Each of those statements would change the ownership of the database to a different person, which wipes away the effect of the previous statement. That is not what you are trying to do, is it?
Go to Top of Page

gsmccoy
Starting Member

USA
7 Posts

Posted - 09/25/2012 :  11:15:25  Show Profile  Reply with Quote
No its not. It's the same effect if you went into the main Security\Logins and clicked user mappings for a specified user. You can then check which databases that user should be mapped to. When you select a database SQL will automatially create that user account under the security folder of the database you selected.

Thanks
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/25/2012 :  11:26:10  Show Profile  Reply with Quote
Ok.

Usually when I want to do that, I use the UI you described, or issue the T-SQL command:
USE databasename
GO
CREATE USER username FOR LOGIN loginname
GO
Go to Top of Page

gsmccoy
Starting Member

USA
7 Posts

Posted - 09/25/2012 :  13:56:22  Show Profile  Reply with Quote
Worked great. Thank you so much
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000