Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Deny impersonate on all logins and users. Expand / Collapse
Author
Message
Posted Thursday, February 7, 2013 1:17 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 25, 2014 5:45 PM
Points: 299, Visits: 921
Hi, we have an audit tool that scans a SQL Server for compliance.

It wants all sorts of permission but I don't want to give it the ability to view the user data.

The scanner documentation wants sysadmin rights for the login but instead I gave it control server and then gave it deny_datareader and deny_datawriter to the user databases.

How can I prevent impersonation or "execute as" of any other logins or users so that it does not run a select on the user databases?

Thanks for reading.
Post #1417330
Posted Thursday, February 7, 2013 1:46 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:15 PM
Points: 12,910, Visits: 32,025
Howard i think this has an example of what you want, but i have not done this myself

http://msdn.microsoft.com/en-us/library/ms186710.aspx


A. Denying IMPERSONATE permission on a login
The following example denies IMPERSONATE permission on the SQL Server login WanidaBenshoof to a SQL Server login created from the Windows user AdvWorks\YoonM.
USE master;
DENY IMPERSONATE ON LOGIN::WanidaBenshoof TO [AdvWorks\YoonM];
GO


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1417343
Posted Thursday, February 7, 2013 2:08 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 25, 2014 5:45 PM
Points: 299, Visits: 921
It is what I want but I can not find a way to deny impersonate to all logins or users without specifying each one.

Thanks for responding.
Post #1417348
Posted Thursday, February 7, 2013 2:15 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:15 PM
Points: 12,910, Visits: 32,025
maybe use the metadata to generate the scripts for you?

this seems to be doing it right?

EXEC sp_msForEachDb 'USE [?];SELECT ''?'' As DbName,''DENY IMPERSONATE ON LOGIN::MyAuditor TO '' + quotename(name) + '';'' FROM sys.database_principals'





Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1417350
Posted Thursday, February 7, 2013 2:24 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 25, 2014 5:45 PM
Points: 299, Visits: 921
Thanks Lowell,

That is definitely an option. Was hoping for a solution that did not require to make a deny for each user and login and then have to remember to run it again each time a login is added.
Post #1417355
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse