Restrict "sysadmin" to access a particular database

Question

Restrict "sysadmin" to access a particular database

shamshad.ali

Hall of Fame

Points: 3850
More actions
March 12, 2018 at 1:39 am

#410311

Here is our organization I want to Restrict some particular sysadmin accounts to access a particular database. Is there any way to do this? any idea?
Shamshad Ali

Viewing 15 posts - 1 through 15 (of 18 total)

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

NorthernSoul SSCertifiable Points: 6869 More actions · Answer 1

shamshad.ali - Monday, March 12, 2018 1:39 AM
Here is our organization I want to Restrict some particular sysadmin accounts to access a particular database. Is there any way to do this? any idea?
Shamshad Ali

No you won't be able to, sysadmins have unlimited access to all databases on the instance. The only way you could do it is to create a new instance and put the restricted database on there or move the database off onto a new database server.

Thanks

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Yes, or just grant db_owner membership in the database in question.

John

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 3

shamshad.ali - Monday, March 12, 2018 1:39 AM
Here is our organization I want to Restrict some particular sysadmin accounts to access a particular database. Is there any way to do this? any idea?
Shamshad Ali

May I ask what is the reason? Is it because the data is sensitive?
😎

shamshad.ali Hall of Fame Points: 3850 More actions · Answer 4

Eirikur Eiriksson - Monday, March 12, 2018 3:42 AM
shamshad.ali - Monday, March 12, 2018 1:39 AM
Here is our organization I want to Restrict some particular sysadmin accounts to access a particular database. Is there any way to do this? any idea?
Shamshad Ali
May I ask what is the reason? Is it because the data is sensitive?
😎

Yes, it might be but to be honest, I do not have exact Idea, my management want this to be done if there is any way. May be they want to apply the same on me. Can anyone do this?

Shamshad Ali

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

shamshad.ali - Monday, March 12, 2018 10:01 AM
Can anyone do this?

just grant db_owner membership in the database in question

John

shamshad.ali Hall of Fame Points: 3850 More actions · Answer 6

John Mitchell-245523 - Monday, March 12, 2018 10:07 AM
shamshad.ali - Monday, March 12, 2018 10:01 AM
Can anyone do this?
just grant db_owner membership in the database in question
John

I know this is stupid question, there are several other server roles we may give to this user except sysadmin
sysadmin vs db_owner? it can not be db_owner because this user then can't perform other sysadmin like tasks, I just want to restrict a database from sysadmin on same instance.
Do you know the difference ?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

Please specify exactly what you need the user to be able to do. First you said it was only for one particular database; now you seem to be saying that server-level permissions are required as well.

John

shamshad.ali Hall of Fame Points: 3850 More actions · Answer 8

John Mitchell-245523 - Monday, March 12, 2018 10:19 AM
Please specify exactly what you need the user to be able to do. First you said it was only for one particular database; now you seem to be saying that server-level permissions are required as well.
John

Yes, When I quoted with "sysadmin" as the title of my question, it was understood man.

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 9

Well if the issue is sensitive data then maybe going with application level encryption for that data would make more sense. Then the sys admin in question would be able to do everything needed in the database but wouldn't actually have access to the data, unless he was also a super user in the application....

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 10

shamshad.ali - Monday, March 12, 2018 10:01 AM
Eirikur Eiriksson - Monday, March 12, 2018 3:42 AM
shamshad.ali - Monday, March 12, 2018 1:39 AM
Here is our organization I want to Restrict some particular sysadmin accounts to access a particular database. Is there any way to do this? any idea?
Shamshad Ali
May I ask what is the reason? Is it because the data is sensitive?
😎
Yes, it might be but to be honest, I do not have exact Idea, my management want this to be done if there is any way. May be they want to apply the same on me. Can anyone do this?
Shamshad Ali

You need to ask them why, sysadmin or sa cannot be contained unless you do data encryption and manage the keys outside the domain (reach) of the system admins.
So, in brief, you need to ask what are the business requirements, what you've been asked is basically "what is the taste of a round fruit"
😎

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 11

shamshad.ali - Monday, March 12, 2018 10:24 AM
John Mitchell-245523 - Monday, March 12, 2018 10:19 AM
Please specify exactly what you need the user to be able to do. First you said it was only for one particular database; now you seem to be saying that server-level permissions are required as well.
John
Yes, When I quoted with "sysadmin" as the title of my question, it was understood man.

Oh, I see - you want the user to be sysadmin, except you want to deny access to certain databases? That isn't possible, but you might try a combination of some of the other server roles, along with db_owner in the database that you do want the user to be able to see. It might be trial and error (in a test environment, of course) until you hit on the right permissions.

John

shamshad.ali Hall of Fame Points: 3850 More actions · Answer 12

ZZartin - Monday, March 12, 2018 10:28 AM
Well if the issue is sensitive data then maybe going with application level encryption for that data would make more sense. Then the sys admin in question would be able to do everything needed in the database but wouldn't actually have access to the data, unless he was also a super user in the application....

I have no idea, Is this a quick and easy solution to implement?

Sue_H SSC Guru Points: 90860 More actions · Answer 13

I think the harder part is that your company has you started in the wrong direction. You can't restrict sysadmin as already mentioned - sysadmin by passes security checks so it wouldn't work. You need to find out what access they would need for whatever the tasks they are going to perform and go from there.

Sue

shamshad.ali Hall of Fame Points: 3850 More actions · Answer 14

Sue_H - Monday, March 12, 2018 11:24 AM
I think the harder part is that your company has you started in the wrong direction. You can't restrict sysadmin as already mentioned - sysadmin by passes security checks so it wouldn't work. You need to find out what access they would need for whatever the tasks they are going to perform and go from there.
Sue

Well the management is no technical, they want to secure their owns, that is good but the security guards after all you need to trust or do protect yourself and get trained.:laugh: