Server Security

Question

Server Security

VastSQL

SSC-Dedicated

Points: 36097
More actions
December 9, 2013 at 8:00 am

#301214

Hi Experts,
We have an admin user(sysadmin) ,its given by network team by adding to a particular group.
How can we restrict that particular user from SQL Server
TIA

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 1

I don't understand your question. You currently have a user that has sysadmin rights to your SQL server and you want to restrict their access? How far do you want to restrict them? They are a member of a group, but you don't want to restrict the entire group, correct?

Please give some more details on your issue.

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 2

Thanks Keith,

You are right ,i want to restrict one particular domain user . Need to make that user less privileged.

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 3

So we have the following: restrict a single user that has sysadmin rights through a group membership. What are the restrictions you want? No access?

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 4

If they are a member of the sysadmin server role you can't. My understanding was that effectively that role bypasses all security checks, so even a deny would be ignored (since it wouldn't even be checked)..

CEWII

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 5

Elliott Whitlow (12/9/2013)
If they are a member of the sysadmin server role you can't. My understanding was that effectively that role bypasses all security checks, so even a deny would be ignored (since it wouldn't even be checked)..

Elliott is correct, but let's see what you want restricted and see if there is a way to at least monitor it so that you know what that single user is doing.

If everyone in that group shouldn't have access to your server then maybe you need to create a second group and only add the members that do need access. Is that a possibility?

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 6

Thanks everyone,

Even i felt the same what Elliot told,but i seriously what to restrict that user to make sure he wont delete a DB.

How can i at least monitor that particular user?

Lowell SSC Guru Points: 323518 More actions · Answer 7

you could add a serverwide DDL trigger that watches for drop database, and issue a rollback command.

that would not prevent a sysadmin from disabling said trigger, nor from restore the "wrong" database over the top of an existing one.

the right thing to do is to build a new role for the domain user, remove sysadmin, and add back the more limited permissions.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 8

If you have access to the summit recordings Bob Pusateri did a whole session on how to keep track of your sysadmin level accounts: http://www.sqlpass.org/SessionResources/SessionDetails.aspx?sid=4386

The best option is to create a new group if you can and only add those users that need access.

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]