Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Deny member of sysadmin user access to databases


Deny member of sysadmin user access to databases

Author
Message
Maddave
Maddave
SSC-Addicted
SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)

Group: General Forum Members
Points: 421 Visits: 1464
Hi, I am not sure if this is possible, but here goes.

Can a user who is a member of the sysadmin role have specific access rights revoked to restrict their access?

The problem I have is, we recently moved a number of databases from our 2005 instance to a new 2008 instance. One of the applications used a sql login which I configured and gave dbowner rights on its database and public rights on the server login. When the application was tested it kept failing with failed logins to SQL Server. After speaking with the application's support team they asked about the SQL Login and said the login should be a member of the sysadmin server role. I said I didn't want it to be as it is a security risk to our database server which is used by lots of other applications. I asked if they could tell me what specific rights the login needed, but they didn't know, only that it needed sysadmin rights. For the minute I have given it sysadmin rights and the application is working fine.

Obviously, I would like to restrict this user a bit, but it is difficult to know what rights it requires as the application company wont/cant tell me. Can you give a login sysadmin roles, but revoke access to the login for certain databases?

Any help would be great.
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22798 Visits: 18262
No. You cannot prevent sa from accessing the databases through permissions.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (7.4K reputation)

Group: Moderators
Points: 7356 Visits: 1917
In this scenario you would want to look at setting up a separate database instance. You could even run on the same server.

K. Brian Kelley
@‌kbriankelley
swathi.kamraj
swathi.kamraj
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 28
Hi,

I would like to know what is the user's role .. basically on that particular db what the user does?? We don't prefer giving sysadmin rights to everyone .
I advice you if a user executing,reading, writing ,give him individual access else create a separate database , name it with different name,keep updating the data depending on the user requirement and give them complete rights.

Let me know if you got any other solution.


Thanks,
Swathi
kevaburg
kevaburg
Right there with Babe
Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)

Group: General Forum Members
Points: 784 Visits: 944
You have fallen into a trap that I did some time ago. At least that is how it sounds.

A software vendor says "I need sysadmin/db_owner" or this software won't run"! I gave them those rights and ended up having to explain why one of them deleted a database they shouldn't have. Embarassing.

Now I ask the vendors what their application user needs to do and use a strict policy based on the application of minimum privilege. They don't like it (especially as most of the vendors don't really know what level of permission their user really needs!) and it makes me unpopular with some developers but then, they aren't the ones that have to pick up the pieces when they make a mistake.

The 'sa' account, sysadmin fixed server role and db_owner fixed database role should never be given out where it isn't strictly needed simply because they can do absolutely everything and privileges cannot be revoked from any of them.

It is always better to create a tailored account even if it means spending time on the phone treaking it until it does exactly what it is supposed to.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search