locked
EXECUTE as LOGIN problem RRS feed

  • Question

  • I have a view which is created from an open query to AD via a linked server. I have a select statement in a reporting services report which uses the "Impersonate the authenticated user after a connection has been made to the data source". By using this option in my report I am implicitly using the SETUSER function which does work with linked servers. 

    To  overcome this problem I used the EXECUTE AS LOGIN function in my report but this only works when the user that runs the report has a singleton login on the source SQL server. My report is to be run my hundreds of users so I don't want to give each and every one of them a singleton login on the SQL server so is there any way that I can allow users within a windows group to run the EXECUTE AS LOGIN function or is there any other way I can allow a windows group to select from the view?

    Wednesday, July 21, 2010 10:30 PM

Answers

  • The cause for this ,  the user or login name specified in EXECUTE AS <context_specification> must exist as a principal in sys.database_principals or sys.server_principals, respectively, or the EXECUTE AS statement fails.

    Unless the caller is the database owner (dbo) , or is a member of the sysadmin fixed server role, the principal must exist even when the user is accessing the database or instance of SQL Server through a Windows group membership.

    For example, assume the following conditions:

    • CompanyDomain\SQLUsers group has access to the Sales database.
    • CompanyDomain\SqlUser1 is a member of SQLUsers and, therefore, has implicit access to the Sales database.

    Although CompanyDomain\SqlUser1 has access to the database through membership in the SQLUsers group, the statement EXECUTE AS USER = 'CompanyDomain\SqlUser1' fails because CompanyDomain\SqlUser1 does not exist as a principal in the database.

    If the user has implicit access to the database or instance of SQL Server through a Windows group membership, the user specified in the EXECUTE AS clause is implicitly created when the module is created when one of the following requirements exist:

    • The specified user or login is a member of the sysadmin fixed server role.

    • The user that is creating the module has permission to create principals.

    When neither of these requirements are met, the create module operation fails.

    Also note that,  If the SQL Server (MSSQLSERVER) service is running as a local account (local service or local user account), it will not have privileges to obtain the group memberships of a Windows domain account that is specified in the EXECUTE AS clause. This will cause the execution of the module to fail.

     

    Source:  BooksOnline

     


    Sivaprasad S http://sivasql.blogspot.com Please click the Mark as Answer button if a post solves your problem!

     

    • Marked as answer by Tom Li - MSFT Sunday, August 1, 2010 8:18 AM
    Thursday, July 22, 2010 9:09 AM

All replies

  • Hmm, I have never done it before by myself, but see if the link helps  you

    http://www.sommarskog.se/grantperm.html


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, July 22, 2010 5:33 AM
  • The cause for this ,  the user or login name specified in EXECUTE AS <context_specification> must exist as a principal in sys.database_principals or sys.server_principals, respectively, or the EXECUTE AS statement fails.

    Unless the caller is the database owner (dbo) , or is a member of the sysadmin fixed server role, the principal must exist even when the user is accessing the database or instance of SQL Server through a Windows group membership.

    For example, assume the following conditions:

    • CompanyDomain\SQLUsers group has access to the Sales database.
    • CompanyDomain\SqlUser1 is a member of SQLUsers and, therefore, has implicit access to the Sales database.

    Although CompanyDomain\SqlUser1 has access to the database through membership in the SQLUsers group, the statement EXECUTE AS USER = 'CompanyDomain\SqlUser1' fails because CompanyDomain\SqlUser1 does not exist as a principal in the database.

    If the user has implicit access to the database or instance of SQL Server through a Windows group membership, the user specified in the EXECUTE AS clause is implicitly created when the module is created when one of the following requirements exist:

    • The specified user or login is a member of the sysadmin fixed server role.

    • The user that is creating the module has permission to create principals.

    When neither of these requirements are met, the create module operation fails.

    Also note that,  If the SQL Server (MSSQLSERVER) service is running as a local account (local service or local user account), it will not have privileges to obtain the group memberships of a Windows domain account that is specified in the EXECUTE AS clause. This will cause the execution of the module to fail.

     

    Source:  BooksOnline

     


    Sivaprasad S http://sivasql.blogspot.com Please click the Mark as Answer button if a post solves your problem!

     

    • Marked as answer by Tom Li - MSFT Sunday, August 1, 2010 8:18 AM
    Thursday, July 22, 2010 9:09 AM
  • The cause for this ,  the user or login name specified in EXECUTE AS <context_specification> must exist as a principal in sys.database_principals or sys.server_principals , respectively, or the EXECUTE AS statement fails.

    Unless the caller is the database owner (dbo) , or is a member of the sysadmin fixed server role, the principal must exist even when the user is accessing the database or instance of SQL Server through a Windows group membership .

    For example, assume the following conditions:

    • CompanyDomain\SQLUsers group has access to the Sales database.
    • CompanyDomain\SqlUser1 is a member of SQLUsers and, therefore, has implicit access to the Sales database.

    Although CompanyDomain\SqlUser1 has access to the database through membership in the SQLUsers group, the statement EXECUTE AS USER = 'CompanyDomain\SqlUser1' fails because CompanyDomain\SqlUser1 does not exist as a principal in the database.

    If the user has implicit access to the database or instance of SQL Server through a Windows group membership, the user specified in the EXECUTE AS clause is implicitly created when the module is created when one of the following requirements exist:

    • The specified user or login is a member of the sysadmin fixed server role.

    • The user that is creating the module has permission to create principals.

    When neither of these requirements are met, the create module operation fails.

    Also note that,  If the SQL Server (MSSQLSERVER) service is running as a local account (local service or local user account ), it will not have privileges to obtain the group memberships of a Windows domain account that is specified in the EXECUTE AS clause. This will cause the execution of the module to fail.

     

    Source:  BooksOnline

     


    Sivaprasad S http://sivasql.blogspot.com Please click the Mark as Answer button if a post solves your problem!

     

    Siva,

     

    I'd read that myself - it was just blindly hoping there was a way around it. Guess there isn't. Thanks for the help anyway.

     

    Cheers

    Craig

    Thursday, July 22, 2010 11:51 PM