<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 Administration
»
Granting sysadmin rights to local admin to a...
24 posts, Page 1 of 3
1
2
3
»
»»
Granting sysadmin rights to local admin to a db
Rate Topic
Display Mode
Topic Options
Author
Message
SQL_Surfer
SQL_Surfer
Posted Tuesday, February 05, 2013 5:20 PM
SSC Veteran
Group: General Forum Members
Last Login: Today @ 3:13 PM
Points: 207,
Visits: 511
I need to give sysadmin rights to a local admin to only one database on the server. There are 100 dbs on this server. But I need to ensure this local admin has sysadmin rights to only one DB. How do I accomplish this?
Thanks for the help.
Post #1416174
opc.three
opc.three
Posted Tuesday, February 05, 2013 6:06 PM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 5:44 PM
Points: 6,703,
Visits: 11,734
There is no such thing as "sysadmin rights to only one database" but I think I know what you mean.
sysadmin
is a Fixed Server Role and being a member of it allows you to do any action on the entire server, including all databases.
It sounds like you only want to grant this person access to one and only one database and allow them to carry out any action within that database. If that is the case then you will want to do the following:
1. In the database instance, create a new
Windows Authentication
Server Login based on their Windows Account. If you cannot use Windows Authentication for them then create a new Server Login using
SQL Server Authentication
which is based on a username and password you choose. Lookup the
CREATE LOGIN
command.
2. Create a new Database User for the new Server Login in the database you want to allow them to access. Lookup the
CREATE USER
command.
3. Add the Database User to the Fixed Database Role named
db_owner
. Lookup the system stored procedure
sys.sp_addrolemember
.
__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Believe you can and you're halfway there.
--Theodore Roosevelt
Everything Should Be Made as Simple as Possible, But Not Simpler
--Albert Einstein
The significant problems we face cannot be solved at the same level of thinking we were at when we created them.
--Albert Einstein
1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples.
--Giordy
Post #1416188
SQL_Surfer
SQL_Surfer
Posted Tuesday, February 05, 2013 8:23 PM
SSC Veteran
Group: General Forum Members
Last Login: Today @ 3:13 PM
Points: 207,
Visits: 511
So, the login should be granted sysadmin role?
Post #1416208
Lynn Pettis
Lynn Pettis
Posted Tuesday, February 05, 2013 9:09 PM
SSC-Insane
Group: General Forum Members
Last Login: Today @ 5:20 PM
Points: 21,602,
Visits: 27,428
SQL_Surfer (2/5/2013)
So, the login should be granted sysadmin role?
No. Doing that will give them sysadmin rights to the entire SQL Server instance.
Lynn Pettis
For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here
or
when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here
and
here
Managing Transaction Logs
SQL Musings from the Desert
Fountain Valley SQL
(My Mirror Blog)
Post #1416219
opc.three
opc.three
Posted Tuesday, February 05, 2013 9:10 PM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 5:44 PM
Points: 6,703,
Visits: 11,734
Er, no.
__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Believe you can and you're halfway there.
--Theodore Roosevelt
Everything Should Be Made as Simple as Possible, But Not Simpler
--Albert Einstein
The significant problems we face cannot be solved at the same level of thinking we were at when we created them.
--Albert Einstein
1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples.
--Giordy
Post #1416220
SQL_Surfer
SQL_Surfer
Posted Tuesday, February 05, 2013 9:46 PM
SSC Veteran
Group: General Forum Members
Last Login: Today @ 3:13 PM
Points: 207,
Visits: 511
I still don't understand how that user has admin rights on that db. That will only make db-owner I thought. Can this user run activity monitor and all? Sorry I am a newbie on this. Truly appreciate ur help.
Post #1416231
Justin Manning SA
Justin Manning SA
Posted Tuesday, February 05, 2013 10:06 PM
Old Hand
Group: General Forum Members
Last Login: Wednesday, May 08, 2013 5:32 AM
Points: 315,
Visits: 124
What exactly does the user need to do?
You should focus on giving the least amount of access.
Get the details of what they are trying to achieve and then we can help you.
Post #1416233
SQL_Surfer
SQL_Surfer
Posted Wednesday, February 06, 2013 5:07 AM
SSC Veteran
Group: General Forum Members
Last Login: Today @ 3:13 PM
Points: 207,
Visits: 511
They need to be able to run activity monitor on that db, take backups, restore only on that db.
Post #1416417
Justin Manning SA
Justin Manning SA
Posted Wednesday, February 06, 2013 5:14 AM
Old Hand
Group: General Forum Members
Last Login: Wednesday, May 08, 2013 5:32 AM
Points: 315,
Visits: 124
dbo on that database should be fine.
With regards to activity monitor, this should be included, but if they want to use sql profiler, you will need to grant alter trace to that user
Post #1416424
opc.three
opc.three
Posted Wednesday, February 06, 2013 10:47 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 5:44 PM
Points: 6,703,
Visits: 11,734
Justin Manning-350368 (2/6/2013)
dbo on that database should be fine.
I am thinking you meant 'membership in db_owner' and not 'dbo.'
You gave them dbo?
With regards to activity monitor, this should be included, but if they want to use sql profiler, you will need to grant alter trace to that user
Simply being a member of the public Fixed Server Role and a member of the db_owner Fixed Database Role in a User database (the result of the 3 steps I outlined above, i.e. making someone a member of db_owner) is not enough for them to take advantage of Activity Monitor.
Errors like
The user does not have permission to perform this action. (Microsoft SQL Server, Error: 297)
will ensue.
Many resources are shared across all databases in SQL Server and the engine does not neatly segment and expose the ability for a Login to monitor all aspects of those shared resources for a particular database from the server level downward via Activity Monitor. If you want to allow someone to use monitor all things happening at the server level then adding them to the sysadmin Fixed Server Role is the likely direction you'll need to go, else you're in for lots of custom programming to expose certain server-level properties while not elevating permissions.
__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Believe you can and you're halfway there.
--Theodore Roosevelt
Everything Should Be Made as Simple as Possible, But Not Simpler
--Albert Einstein
The significant problems we face cannot be solved at the same level of thinking we were at when we created them.
--Albert Einstein
1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples.
--Giordy
Post #1416641
« Prev Topic
|
Next Topic »
24 posts, Page 1 of 3
1
2
3
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
