July 16, 2007 at 7:52 am
Hi All,
We have SQL Server 2005 on Server, i want to give sysadmin rights to domain users, so they can connect to DB from their local PC and work on it.
Can anyone give me Steps wise procedure to do it. The users should be able to create DB and make changes to DB.
Thanks in Advance,
July 16, 2007 at 9:01 am
You don't have to give sysadmin rights to the domain users.
Ask your network admin to create a global group with people who have to access your SQL Server. In Management Studio expand server name, expand Security, Right-click Logins, select New. In the Login - New window select Windows authentication, and enter the global group name under Login Name. Do not give sysadmin rights, go to Server Roles page in New-Login window and give dbcreator role the this new login. Then add rights as appropriate when they ask.
Regards,Yelena Varsha
July 17, 2007 at 12:19 am
dbcreator role should be the one you should give if you give sysadmin then the users cando anything with sql server so better avoid that.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 17, 2007 at 12:57 am
Thanks All,
As per your instruction, I created New Global Group and added Domain Users to it. And gave access as dbowner to Global Group.
Now the problem is I'm able to connect to the server, but I can see only default Databases(master, model, msdb, tempdb) not other Databases, do I need to give any other access to global group. And after connecting to the Server, its not starting. When I right click and select "Connect" its doing nothing.
Urgent help would be appreciated.
Thanks in Advance.
July 17, 2007 at 3:28 am
You would need to give access to other user databases as permission needs. the second part of your question is not clear. can you post briefly.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 17, 2007 at 5:20 am
Thanks Sugesh For Your Helpful Replies,
Regarding my second part of Question.
When I login with windows Authentication, it allowing me. But when I right click the connection to start the service/database. The Start,Stop,Restart Option is Greyed Out.
And in the available database's list I can see only Defaul Databases, not other Databases.
July 17, 2007 at 5:43 am
Only members of sysadmin in sql server will have the option to start/stop the sql server services and since the user you have created has only dbcreate privilege he will be able to do things in the db he creates and he will not be able to start/stop services.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 17, 2007 at 7:25 am
Hi Sugesh,
I added the user to SysAdmin group, even after that he is not able to start/stop sql services.
He cant see other databases eventhough I gave him sysadmin access to those databases.
Thanks in Advance
July 17, 2007 at 7:45 am
Don't mistake me have you added him in the sysadmin role of sql server or the windows group. Add him in the sysadmin role of sql server.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 17, 2007 at 4:51 pm
In order to be able to stop/start services, the Windows user must be a member of the local administrators or power users groups on the Windows side. However, if this is a shared SQL Server, you probably don't want everyone with the ability to stop and restart the SQL Server.
Domain Users is a global group and can be given access. If indeed you need every domain user to have access, you don't need the new group.
K. Brian Kelley
@kbriankelley
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply