Monday, March 12, 2012 9:52 PM
I have created stored procedure:
CREATE PROCEDURE [dbo].[CreateLogin] @username sysname, @pwd nvarchar(20) WITH EXECUTE AS 'manager' AS BEGIN SET NOCOUNT ON DECLARE @SQL NVARCHAR(300); SET @SQL = 'USE MASTER; CREATE LOGIN ' + @username + ' WITH PASSWORD = ''' + @pwd + ''', DEFAULT_DATABASE=[CallData], DEFAULT_LANGUAGE=[ENGLISH], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'; EXECUTE(@SQL); SET @SQL = 'USE CALLDATA; CREATE USER ' + @username + ' FOR LOGIN ' + @username; EXECUTE(@SQL); EXEC sp_addrolemember 'db_datareader', @username EXEC sp_addrolemember 'db_datawriter', @username END;
Have created login 'manager' for the sql server and user 'manager' for database CallData. when I fire up the t-sql as a statement (not as a sp) I can create the login and user without any problem, but cannot fire (even as a 'sa') the stored procedure getting error message:
Msg 15247, Level 16, State 1, Line 1 User does not have permission to perform this action.
when I remove from the sp the line:
WITH EXECUTE AS 'manager'
there is no problem to fire the sp. Any sugestion?
Monday, March 12, 2012 11:17 PMModerator
Those statements are not exactly SELECT queries in the sp!
For example, CREATE LOGIN requires: ALTER ANY LOGIN permission on the
server or membership in the securityadmin fixed server role.
What is the role membership of login manager? User manager?
BOL article: "EXECUTE AS Clause (Transact-SQL)
By specifying the context in which the module is executed, you can control which user account the Database Engine uses to validate permissions on objects that are referenced by the module. This provides additional flexibility and control in managing permissions across the object chain that exists between user-defined modules and the objects referenced by those modules. Permissions must be granted to users only on the module itself, without having to grant them explicit permissions on the referenced objects. Only the user that the module is running as must have permissions on the objects accessed by the module.
Specifies the context to be impersonated is a user in the current database. The scope of impersonation is restricted to the current database. A context switch to a database user does not inherit the server-level permissions of that user.
While the context switch to the database user is active, any attempt to access resources outside of the database will cause the statement to fail. This includes USE database statements, distributed queries, and queries that reference another database that uses three- or four-part identifiers. To extend the scope of the context switch outside the current database, see Extending Database Impersonation by Using EXECUTE AS. "
Kalman Toth SQL SERVER & BI TRAINING
- Edited by Kalman TothMicrosoft Community Contributor, Moderator Tuesday, March 13, 2012 1:27 AM
Monday, March 12, 2012 11:20 PM
This is because with the EXECUTE AS clause you are impersonating a database user, in which case you are sandboxed into the cucrrent database.
There are ways to work around that, but they have security implications. Or you could use certificate signing which is much better.
Read this article on my web site:
It's long, but it will tell you everything you need to know.
Erland Sommarskog, SQL Server MVP, firstname.lastname@example.org
Monday, March 12, 2012 11:41 PM
the server role for login manager is 'public' and 'securityadmin'
the database role for user manager is 'db_owner'
Wednesday, March 14, 2012 10:55 PM
the best ever answer and understandable explanation is there:
thank you very much to all who tried help
- Marked As Answer by yun58711 Wednesday, March 14, 2012 10:55 PM