Post reply

Deny impersonate on all logins and users.

  • February 7, 2013 at 1:17 pm

    #394141

    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.

  • February 7, 2013 at 1:46 pm

    #1585660

    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!

  • February 7, 2013 at 2:08 pm

    #1585664

    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.

  • February 7, 2013 at 2:15 pm

    #1585665

    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!

  • February 7, 2013 at 2:24 pm

    #1585670

    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.

Viewing 5 posts - 1 through 4 (of 4 total)

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