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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Alter autorization on database for multiple login

Author  Topic 

gsmccoy
Starting Member

7 Posts

Posted - 2012-09-25 : 10:00:13
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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-25 : 10:50:45
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

7 Posts

Posted - 2012-09-25 : 11:15:25
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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-25 : 11:26:10
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

7 Posts

Posted - 2012-09-25 : 13:56:22
Worked great. Thank you so much
Go to Top of Page
   

- Advertisement -