February 5, 2013 at 5:20 pm
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.
February 5, 2013 at 6:06 pm
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
February 5, 2013 at 8:23 pm
So, the login should be granted sysadmin role?
February 5, 2013 at 9:09 pm
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.
February 5, 2013 at 9:10 pm
Er, no.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 5, 2013 at 9:46 pm
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.
February 5, 2013 at 10:06 pm
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.
February 6, 2013 at 5:07 am
They need to be able to run activity monitor on that db, take backups, restore only on that db.
February 6, 2013 at 5:14 am
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
February 6, 2013 at 10:47 am
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.'
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
February 6, 2013 at 11:27 am
would this not work? I have a user who does not have SA on the box and granting them view server state allowed them to use activity monitor.
GRANT VIEW SERVER STATE TO [Domain\Username]
Though adding it to a role might be better. This can also be added as a securable via gui.
.
February 6, 2013 at 11:34 am
VIEW SERVER STATE might work if only view is required. It offers nothing in the way of managing and allows the user to see things related to other databases, not just the one they are a member of db_owner.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 6, 2013 at 11:41 am
That is 100% correct. It will however, allow them to view the activity monitor (all items). so with your permission change to allow them to own that specific user database and the view. That might cover all of his requirements with least privilege.
.
February 6, 2013 at 11:51 am
Once we moved beyond the terminology of "sysadmin" the way I am reading the requirement "But I need to ensure this local admin has sysadmin rights to only one DB." means they should not be allowed to see or do anything with any databases on the instance other than the one in which they are a member of db_owner. VIEW SERVER STATE would not adhere to that requirement.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 6, 2013 at 12:19 pm
In that respect, you are correct. If he is not allowed to view any other database through activity monitor, then this would not work. I don't know if you can restrict activity monitor to a single database... even if so, would it contain correct information since you're not getting the full picture? I think this one would be subjective if activity monitor was required... can he view other items.
.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply