Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Deny impersonate on all logins and users.


Deny impersonate on all logins and users.

Author
Message
PHXHoward
PHXHoward
Old Hand
Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)

Group: General Forum Members
Points: 371 Visits: 1214
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.
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14984 Visits: 39008
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

--
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!

PHXHoward
PHXHoward
Old Hand
Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)

Group: General Forum Members
Points: 371 Visits: 1214
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.
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14984 Visits: 39008
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

--
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!

PHXHoward
PHXHoward
Old Hand
Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)

Group: General Forum Members
Points: 371 Visits: 1214
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search