January 8, 2016 at 1:49 pm
Hi Folks,
Please help on this.
My MS SQL version is MS SQL 2012 EE.
The problem is when I check the "Server Roles" and "Securables" of this login, "NCL\spdfarmadm" on instance level, I can see this login has "dbcreator", "public", and "securityadmin" role and has the following "Securables".
"Connect SQL"
"Control server"
"View server state"
If I ran the sql query below, I can see this login has 31 permissions.
use MASTER
go
EXECUTE AS LOGIN = 'NCL\spdfarmadm';
SELECT * FROM sys.fn_my_permissions(default, 'SERVER');
entity_namesubentity_namepermission_name
serverCONNECT SQL
serverSHUTDOWN
serverCREATE ENDPOINT
serverCREATE ANY DATABASE
serverCREATE AVAILABILITY GROUP
serverALTER ANY LOGIN
serverALTER ANY CREDENTIAL
serverALTER ANY ENDPOINT
serverALTER ANY LINKED SERVER
serverALTER ANY CONNECTION
serverALTER ANY DATABASE
serverALTER RESOURCES
serverALTER SETTINGS
serverALTER TRACE
serverALTER ANY AVAILABILITY GROUP
serverADMINISTER BULK OPERATIONS
serverAUTHENTICATE SERVER
serverEXTERNAL ACCESS ASSEMBLY
serverVIEW ANY DATABASE
serverVIEW ANY DEFINITION
serverVIEW SERVER STATE
serverCREATE DDL EVENT NOTIFICATION
serverCREATE TRACE EVENT NOTIFICATION
serverALTER ANY EVENT NOTIFICATION
serverALTER SERVER STATE
serverUNSAFE ASSEMBLY
serverALTER ANY SERVER AUDIT
serverCREATE SERVER ROLE
serverALTER ANY SERVER ROLE
serverALTER ANY EVENT SESSION
serverCONTROL SERVER
I tried to revoke some of permissions.
For example,
use MASTER
go
REVOKE SHUTDOWN TO [NCL\SPDFarmAdm];
The revoke command was successfully complete but I still can see it if I run the select query above.
Do you know why?
Thanks!
January 8, 2016 at 2:08 pm
Because it's not an explicitly granted permission. It's part of what CONTROL SERVER grants (which is basically sysadmin rights). You can DENY the right, and the DENY will overrule the GRANT, but a REVOKE just negated a GRANT or DENY and that login didn't have a GRANT on that permission.
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
January 8, 2016 at 2:52 pm
Thank you, GilaMonster, for your quick response.
Yes, I ran the "DENY" command.
use MASTER
go
DENY SHUTDOWN TO [NCL\SPDFarmAdm];
DENY ADMINISTER BULK OPERATIONS TO [NCL\SPDFarmAdm];
DENY CREATE ENDPOINT TO [NCL\SPDFarmAdm];
DENY CREATE AVAILABILITY GROUP TO [NCL\SPDFarmAdm];
DENY ALTER ANY CREDENTIAL TO [NCL\SPDFarmAdm];
DENY ALTER ANY ENDPOINT TO [NCL\SPDFarmAdm];
DENY ALTER ANY LINKED SERVER TO [NCL\SPDFarmAdm];
DENY ALTER ANY CONNECTION TO [NCL\SPDFarmAdm];
DENY ALTER ANY DATABASE TO [NCL\SPDFarmAdm];
DENY ALTER RESOURCES TO [NCL\SPDFarmAdm];
DENY ALTER SETTINGS TO [NCL\SPDFarmAdm];
DENY ALTER TRACE TO [NCL\SPDFarmAdm];
DENY ALTER ANY AVAILABILITY GROUP TO [NCL\SPDFarmAdm];
DENY AUTHENTICATE SERVER TO [NCL\SPDFarmAdm];
DENY EXTERNAL ACCESS ASSEMBLY TO [NCL\SPDFarmAdm];
DENY VIEW ANY DEFINITION TO [NCL\SPDFarmAdm];
DENY CREATE DDL EVENT NOTIFICATION TO [NCL\SPDFarmAdm];
DENY CREATE TRACE EVENT NOTIFICATION TO [NCL\SPDFarmAdm];
DENY ALTER ANY EVENT NOTIFICATION TO [NCL\SPDFarmAdm];
DENY ALTER SERVER STATE TO [NCL\SPDFarmAdm];
DENY UNSAFE ASSEMBLY TO [NCL\SPDFarmAdm];
DENY ALTER ANY SERVER AUDIT TO [NCL\SPDFarmAdm];
DENY CREATE SERVER ROLE TO [NCL\SPDFarmAdm];
DENY ALTER ANY SERVER ROLE TO [NCL\SPDFarmAdm];
DENY ALTER ANY EVENT SESSION TO [NCL\SPDFarmAdm];
DENY CONTROL SERVER TO [NCL\SPDFarmAdm];
The problem is after I deny the following permission, I can not execute
use MASTER
go
EXECUTE AS LOGIN = 'NCL\spdfarmadm';
SELECT * FROM sys.fn_my_permissions(NULL, 'SERVER');
Msg 916, Level 14, State 1, Line 1
The server principal "NCL\spdfarmadm" is not able to access the database "master" under the current security context.
January 8, 2016 at 3:04 pm
I found the "VIEW ANY DATABASE" permission was dropped for some reason.
But when I ran to add it. The command below was successfully completed.
GRANT VIEW ANY DATABASE TO [NCL\SPDFarmAdm];
But when I checked, this login still doesn't have the "VIEW ANY DATABASE" permission and got the same error below.
Msg 916, Level 14, State 1, Line 1
The server principal "NCL\spdfarmadm" is not able to access the database "master" under the current security context.
January 8, 2016 at 3:12 pm
Weird, at this time, I revoked those permission after I deny.
use [master]
GO
REVOKE ADMINISTER BULK OPERATIONS TO [NCLPROD\spdfarmadm] AS [sa]
GO
use [master]
GO
REVOKE ALTER ANY AVAILABILITY GROUP TO [NCLPROD\spdfarmadm] AS [sa]
GO
use [master]
GO
REVOKE ALTER ANY CONNECTION TO [NCLPROD\spdfarmadm] AS [sa]
GO
use [master]
GO
REVOKE ALTER ANY CREDENTIAL TO [NCLPROD\spdfarmadm] AS [sa]
GO
use [master]
GO
REVOKE ALTER ANY DATABASE TO [NCLPROD\spdfarmadm] AS [sa]
GO
use [master]
GO
REVOKE ALTER ANY ENDPOINT TO [NCLPROD\spdfarmadm] AS [sa]
GO
use [master]
GO
REVOKE ALTER ANY EVENT NOTIFICATION TO [NCLPROD\spdfarmadm] AS [sa]
GO
use [master]
GO
REVOKE ALTER ANY EVENT SESSION TO [NCLPROD\spdfarmadm] AS [sa]
GO
use [master]
GO
REVOKE ALTER ANY LINKED SERVER TO [NCLPROD\spdfarmadm] AS [sa]
GO
use [master]
GO
REVOKE ALTER ANY SERVER AUDIT TO [NCLPROD\spdfarmadm] AS [sa]
GO
use [master]
GO
REVOKE ALTER ANY SERVER ROLE TO [NCLPROD\spdfarmadm] AS [sa]
GO
use [master]
GO
REVOKE ALTER RESOURCES TO [NCLPROD\spdfarmadm] AS [sa]
GO
use [master]
GO
REVOKE ALTER SERVER STATE TO [NCLPROD\spdfarmadm] AS [sa]
GO
use [master]
GO
REVOKE ALTER SETTINGS TO [NCLPROD\spdfarmadm] AS [sa]
GO
use [master]
GO
REVOKE ALTER TRACE TO [NCLPROD\spdfarmadm] AS [sa]
GO
use [master]
GO
REVOKE AUTHENTICATE SERVER TO [NCLPROD\spdfarmadm] AS [sa]
GO
use [master]
GO
REVOKE CONTROL SERVER TO [NCLPROD\spdfarmadm] AS [sa]
GO
use [master]
GO
REVOKE CREATE AVAILABILITY GROUP TO [NCLPROD\spdfarmadm] AS [sa]
GO
use [master]
GO
REVOKE CREATE DDL EVENT NOTIFICATION TO [NCLPROD\spdfarmadm] AS [sa]
GO
use [master]
GO
REVOKE CREATE ENDPOINT TO [NCLPROD\spdfarmadm] AS [sa]
GO
use [master]
GO
REVOKE CREATE SERVER ROLE TO [NCLPROD\spdfarmadm] AS [sa]
GO
use [master]
GO
REVOKE CREATE TRACE EVENT NOTIFICATION TO [NCLPROD\spdfarmadm] AS [sa]
GO
use [master]
GO
REVOKE EXTERNAL ACCESS ASSEMBLY TO [NCLPROD\spdfarmadm] AS [sa]
GO
use [master]
GO
REVOKE SHUTDOWN TO [NCLPROD\spdfarmadm] AS [sa]
GO
use [master]
GO
REVOKE UNSAFE ASSEMBLY TO [NCLPROD\spdfarmadm] AS [sa]
GO
use [master]
GO
REVOKE VIEW ANY DEFINITION TO [NCLPROD\spdfarmadm] AS [sa]
GO
Now I re-ran the query below and it is good now.
EXECUTE AS LOGIN = 'NCL\spdfarmadm';
SELECT * FROM sys.fn_my_permissions(default, 'SERVER');
entity_namesubentity_namepermission_name
serverCONNECT SQL
serverCREATE ANY DATABASE
serverALTER ANY LOGIN
serverVIEW ANY DATABASE
serverVIEW SERVER STATE
Thanks again!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply