How create new logins with admin privileges

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 2000 Forums

SQL Server Administration (2000)

How create new logins with admin privileges

Author

Topic

Aytan
Starting Member

1 Post

Posted - 2002-08-30 : 16:58:42

Hi:

I am working on application which requires to assign administrator access to sql databases to specific users.

I wrote code and tested it in Query Analyzer and it worked properly. When I used this code in stored procedure it didn't work. So, here what exactly I need:

1. Create login;
2. Add this user to all databases on Server;
3. Give Systems Admininistrator server role;
4. and assigned to db_owner role;

I do it by the following:

EXEC sp_addlogin 'TestUser', 'TestPassword'

And I repeat the following code for each database which I want this user to be added
In this example I will use only two databases MYDB and master db

---------------------------------------------------------
Use MyDB

EXEC sp_defaultdb @userName, 'MyDB'
EXEC sp_grantdbaccess @userName
EXEC sp_addrolemember db_owner, @userName
EXEC sp_addsrvrolemember @userName, 'sysadmin'
-------------------------------------------------------------------

Use master

EXEC sp_defaultdb @userName, 'master'
EXEC sp_grantdbaccess @userName
EXEC sp_addrolemember db_owner, @userName
EXEC sp_addsrvrolemember @userName, 'sysadmin'

-----------------------------------------------------------------

When I use this code in stored procedure I get eror about 'USE' statement, which cannot be used in stored procedure.

So, does anyone can help me find what I can use to get the same result?
Or probably tehre is different way to achieve the same result?

Thank you in advance for help.

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-08-30 : 17:34:49

All you need to do is add the login to the Sytem Administrator fixed server database role , the rest is irrelavent.

However, I would seriously question why they need sysadmin access, just add them to the db_owner fixed database role and they will be able to danything in the database.

HTH
Jasper Smith

Edited by - jasper_smith on 08/31/2002 16:34:02

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources