August 29, 2016 at 3:20 am
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
August 29, 2016 at 3:22 am
ALTER DATABASE MyDatabase SET SINGLE_USER
-- Gianluca Sartori
August 29, 2016 at 3:45 am
Will that limit the access to the db, or will that allow the 1st user who created a session to that db specific access
August 29, 2016 at 3:55 am
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
August 29, 2016 at 5:20 am
Thanks I tried that, but if user has sysadmin role he still has access to the db 🙁
August 29, 2016 at 5:34 am
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
August 29, 2016 at 6:17 am
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
August 29, 2016 at 6:34 am
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
August 29, 2016 at 6:35 am
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
August 29, 2016 at 7:46 am
GilaMonster (8/29/2016)
SQL_Student (8/29/2016)
will try this thanks, the issue is developers require sysadmin role on this specific boxThen that's the first thing that you need to fix, with support from management.
+Infinity to that.
--Jeff Moden
August 29, 2016 at 7:48 am
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
August 29, 2016 at 7:58 am
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
August 30, 2016 at 12:42 am
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)
August 30, 2016 at 8:41 am
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
August 30, 2016 at 11:52 pm
Awesome stuff, will defenitely try that out.
Thanks a lot for your assistance
and Thanks to everyone else who contributed much appreciated:-D
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply