Create new administrator - 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

SQL Server 2008 Forums

SQL Server Administration (2008)

Create new administrator

Author

Topic

kukstern
Starting Member

6 Posts

Posted - 2010-07-12 : 10:01:31

Hi

I'm a newbie in SQL
I need to create a new administrator for a consultant. He's allowed to make a new db and read from others, but not to edit the others.

How do I do that?

/Kennet

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-07-12 : 11:09:25

EXEC sp_addsrvrolemember 'user name here', 'sysadmin';

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

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2010-07-12 : 15:42:39

russell , by making him 'sysadmin' , that would give the user edit \ and a whole bunch of other elevated rights.
To allow the user 'create new db' role , and read rights do:
--this role will allow the user to create new databases & do alteration on dbs they own
EXEC master..sp_addsrvrolemember @loginame = N'user name', @rolename = N'dbcreator'
GO
--to allow read rights on other databases do:
USE [my_db1]
GO
CREATE USER [a user] FOR LOGIN [mylogin]
GO
USE [my_db1]
GO
EXEC sp_addrolemember N'db_datareader', N'a user'
GO

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-07-12 : 15:56:23

OP asked how to create an administrator.

edit: perhaps i misunderstood. i thought the OP meant the consultant already had the permissions you showed how to create Jack

kukstern
Starting Member

6 Posts

Posted - 2010-07-13 : 02:11:43

Is it not possible to make it from the console/gui?

As I wrote I'm a total newbie, and what you (jackv) wrote doesn't make sense to me.

As I can see it's possible to make a SQL user login or just use the Windows authentication. I have already created a domain account for the consultant, but I'm not sure to use that or use a SQL login.

/Kennet

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2010-07-13 : 04:25:58

kukstern, yes it is possible you can add these permissions to the logon via the GUI. I posted the sql procedures that would allow you to add from the QueryAnalyzer.
Firstly, the decision to use SQL Login or Windows Account is dependant on you or your organisation manage users . As you have already created a Domain Account , I'll assume you'll be using this accounnt to create the logon account. So , the first thing to do is to create a Logon account. Once you've connected to your SQL Server , go to Security|Logins and you'll see some existing Logins- if you right click on Logins you get an option on creating a New Login. Use the Windows Authentication
Secondly, once you've created the Login account , it will appear in the list . Right click the Login account , expand Server Roles , and select DbCreator -
Thirdly, Right click again on Logon account , this time , expand User Mappings- select databases you want the user to have access - and select db_datareader.

It is worth reading up on the different roles | permissions etc

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2010-07-13 : 04:28:11

russell: yes I agree , i wasn't 100% sure initially, i assumed he meant "administrator " in the generic sense

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

kukstern
Starting Member

6 Posts

Posted - 2010-07-13 : 05:27:19

#jack

Nice its working now.

But had to find the right user with rights to create a user. Our normal administrator on SQL is on vacation :-)

Thank you for your help.

/Kennet

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2010-07-13 : 13:11:11

Your welcome , not a problem

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources