Restrict access on single database from admin user

Question

Post reply

Restrict access on single database from admin user

SQL_Student

SSCrazy

Points: 2536
More actions
August 29, 2016 at 3:20 am

#312535

Hi people
Strange question I know, but wanted to know if this is possible
yes I do know that sysadmin user has full access on the SQL server
Restricting all access on a Single database from a user with sysadmin role on SQL server :hehe:
If not possible any suggestion of how I can restrict access on a single database from user with sysadmin role

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

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

ALTER DATABASE MyDatabase SET SINGLE_USER

-- Gianluca Sartori

SQL_Student SSCrazy Points: 2536 More actions · Answer 2

Will that limit the access to the db, or will that allow the 1st user who created a session to that db specific access

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

It will allow only one user at a time to access the database.

If you want to restrict access to only db owners (any number of them at the same time), you can use ALTER DATABASE MyDatabase SET RESTRICTED_USER

-- Gianluca Sartori

SQL_Student SSCrazy Points: 2536 More actions · Answer 4

Thanks I tried that, but if user has sysadmin role he still has access to the db 🙁

Lowell SSC Guru Points: 323504 More actions · Answer 5

SQL_Student (8/29/2016)
Thanks I tried that, but if user has sysadmin role he still has access to the db 🙁

in general, If someone has sysadmin access, you cannot restrict access to anything; permissions are shortcutted so you cannot deny access.

you must remove sysadmin access, and replace it with something similar.

for example, creating a Role like this:

CREATE ROLE [AlmostOwners]

EXEC sp_addrolemember N'db_ddladmin', N'AlmostOwners'

EXEC sp_addrolemember N'db_datareader', N'AlmostOwners'

EXEC sp_addrolemember N'db_datawriter', N'AlmostOwners'

--can the users EXECUTE procedures? uncomment if true

GRANT EXECUTE TO [AlmostOwners]

--allow the users to see view proc and function definitions

Grant View Definition ON SCHEMA::[dbo] To [AlmostOwners]

put that role in every database, and make the login in question a user in all the databases except the "protected" database.

most permissions you see out there grant more than they really need, simply because it's easier.

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!

SQL_Student SSCrazy Points: 2536 More actions · Answer 6

will try this thanks, the issue is developers require sysadmin role on this specific box but I would like to add my monitoring db there without them havin access to it....Currently I'm stuck with silly privelege issues

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

SQL_Student (8/29/2016)
will try this thanks, the issue is developers require sysadmin role on this specific box

Then that's the first thing that you need to fix, with support from management.

There is no need for developers to have sysadmin permissions to a production server (and in cases that they do, there's often 'unexplained' issues). DB_owner on dev databases, sure, I could possibly see a need for more than DB_owner on dev, but not on a production server.

I would suggest that you start by having a chat with the dev team leads/managers and find out exactly what they do on that server. Once you know that, you can construct a non-sysadmin role that allows them to do what they need to without being sysadmin.

but I would like to add my monitoring db there without them havin access to it....

Not while they're sysadmin. A sysadmin has all permissions and nothing can be denied to them

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

spaghettidba SSC Guru Points: 105732 More actions · Answer 8

spaghettidba (8/29/2016)
It will allow only one user at a time to access the database.
If you want to restrict access to only db owners (any number of them at the same time), you can use ALTER DATABASE MyDatabase SET RESTRICTED_USER

I'm sorry, I completely misread your question.

As Lowell said, there is no way to restrict the permissions of sysadmin users.

-- Gianluca Sartori

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 9

GilaMonster (8/29/2016)
SQL_Student (8/29/2016)
will try this thanks, the issue is developers require sysadmin role on this specific box
Then that's the first thing that you need to fix, with support from management.

+Infinity to that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eric M Russell SSC Guru Points: 125522 More actions · Answer 10

You'll need to create this database on another instance for which you are the only SYSADMIN. Also, you'll need to enable transparent database encryption (TDE) on this database to prevent the other DBA from copying the .mdf file and attaching it to another instance.

CREATE CERTIFICATE MySecretCertificate

WITH SUBJECT = 'Certificate to encrypt MySecretDB database key';

GO

BACKUP CERTIFICATE MySecretCertificate

TO FILE = '\\MySecretFolder\MySecretCertificate.certificate';

GO

USE MySecretDB;

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE MySecretCertificate;

GO

ALTER DATABASE MySecretDB SET ENCRYPTION ON;

GO

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Eric M Russell SSC Guru Points: 125522 More actions · Answer 11

Azure SQL and SQL Server 2016 also have a new feature called Always Encrypted, which in a nutshell is a combination of TDE and transparent column level encryption where the encryption keys are contained in an external KeyStore managed by a security administrator. This too can be used to restrict query access from the DBA. But this seature isn not avilable in version 2012.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

SQL_Student SSCrazy Points: 2536 More actions · Answer 12

Thanks that seems interesting, have never used that before... Will that encryption allow the database to be accessed remotely (eg allowing other instances to write into that db with a restritced logon)

Eric M Russell SSC Guru Points: 125522 More actions · Answer 13

SQL_Student (8/30/2016)
Thanks that seems interesting, have never used that before... Will that encryption allow the database to be accessed remotely (eg allowing other instances to write into that db with a restritced logon)

What TDE does is encrypt the .mdf database files; it mitigates the scenario where someone (like another system administrator) gains access to the physical files. Similar in concept to table/index compression, it is transparent to the users in terms of authentication, authorization, and querying. If you have a scenario where there are multiple system administators, and there is a specific database that should accessed only by some administrators, then you want to place that database on a separate instance, enable TDE, and then only grant login to the appropriate administrators. You also want to insure that the backup certificate file is stored somewhere where only athorized administrators can get to it, because that certificate can be used to restore the database on yet another instance.

Actually, this super-secret database and it's SQL Server instance should be installed on a separate server where the unauthorized administrators are not a local server admin. The problem is that, if someone is a local admin on a server, then they can restart SQL Server in single-user mode and then login to the instance that way.

Connect to SQL Server When System Administrators Are Locked Out

https://msdn.microsoft.com/en-us/library/dd207004.aspx

So, just to recap, you need to:

1. Create this database on a separate SQL Server instance where only you are the sysadmin

2. The instance should be hosted on a separate Windows server where only you are the local admin

3. You need to enable TDE on that database.

However, trying to protect an on-premises database from an on-premises DBA is like trying to protect the President from the possibility of a rogue Secret Service Agent working in the White House; if they are determined; the breach is simply going to happen eventually. So, if you're concerned in earnest about protecting this database from a rogue sysadmin administrator, then you probably want to keep it behind a separate firewall or move it off premises to Microsoft Azure or AWS.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

SQL_Student SSCrazy Points: 2536 More actions · Answer 14

Awesome stuff, will defenitely try that out.

Thanks a lot for your assistance

and Thanks to everyone else who contributed much appreciated:-D