October 27, 2011 at 4:34 pm
Thanks, Jeff.
The database was originally owned by my Windows login which is a member of sysadmin. However, I went and changed the owner to sa. I then altered my sproc to add the EXECUTE AS OWNER clause and comment out the EXECUTE AS LOGIN = 'proxyuser' / REVERT statements within the sproc.
I still get the same errors, saying the user doesn't have the permission.
From the article I linked on previous page, the author says that EXECUTE AS clause for sprocs works only at user level and not at login. It also seems (and I hope I'm wrong) that changing even to otherwise higher-privileged database user would prevent you from gaining the permissions of the associated login. Not sure if this demonstrates the problem:
EXECUTE AS USER = 'dbo';
EXECUTE AS LOGIN = 'sa';
BEGIN TRANSACTION;
CREATE LOGIN tester WITH PASSWORD = 'p@ssword1';
ROLLBACK;
REVERT;
REVERT;
If I remove the EXECUTE AS USER / REVERT block, this will succeed, otherwise, it fails with error that server principal 'sa' doesn't exist, cannot be impersonated or does not have the permission.
Also, to be completely sure, I removed the EXECUTE AS clause from my sprocs completely to see if sa' privileges would be implicitly assumed by the sprocs, and no joy for that one.
I'll gladly admit that I may not have a complete understanding of ownership chaining and hope someone could point out if I may have had missed a crucial step. that's needed to get the chain to extend all back to the original server principal and crucial the CREATE LOGIN statement.
Just so we have it out there, here's my working sproc:
ALTER PROCEDURE [dbo].[aspAddUser] (
@newuser nvarchar(50)
,@NewPassword nvarchar(50)
,@NewRoleID int
) AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
DECLARE @stmt nvarchar(MAX);
DECLARE @pdef nvarchar(MAX);
DECLARE @res bit;
DECLARE @user sysname;
DECLARE @pwd nvarchar(MAX);
DECLARE @role sysname;
SET @user = QUOTENAME(@NewUser);
SET @pwd = REPLACE(@NewPassword, '''', '''''');
SET @role = QUOTENAME((
SELECT p.[name]
FROM sys.database_principals AS p
WHERE p.principal_id = @NewRoleID
));
SET @res = 0;
--EXECUTE AS LOGIN = 'proxyuser';
BEGIN TRY
BEGIN TRANSACTION;
SET @stmt = 'USE [master]; CREATE LOGIN ' + @user + ' WITH PASSWORD = ''' + @pwd + ''', DEFAULT_DATABASE=[MyDatabase], CHECK_POLICY = ON, CHECK_EXPIRATION = ON;';
EXEC (@stmt);
SET @stmt = 'CREATE USER ' + @user + ' FOR LOGIN ' + @user + ' WITH DEFAULT_SCHEMA=[dbo];';
EXEC (@stmt);
SET @stmt = 'EXEC sys.sp_addrolemember ' + @role + ', ' + @user + ';';
EXEC (@stmt);
IF XACT_STATE() = 1
BEGIN
COMMIT TRANSACTION;
SET @res = 1;
END;
END TRY
BEGIN CATCH
IF XACT_STATE() = -1
BEGIN
SELECT ERROR_MESSAGE();
ROLLBACK TRANSACTION;
SET @res = 0;
END;
END CATCH;
--REVERT;
SELECT @res;
END;
So far, the only way I was able to get it to work is when I grant IMPERSONATE permission to the proxyuser _and_ NOT use EXECUTE AS clause within my sproc header at all. The proxyuser has to have ALTER ANY LOGIN permission for CREATE LOGIN statement (BOL says either ALTER ANY LOGIN or ALTER LOGIN but I don't think the latter is possible since you can't alter a login that doesn't exist yet).
O.T.: I originally started using sp_executesql in hopes of parameterizing the dynamic statements but apparently this does not work in all cases like the @user variable for CREATE LOGIN statement. Am I mistaken on that point?
October 27, 2011 at 5:39 pm
You've made life entirely too difficult for yourself. 😉 Unless you're using xp_CmdShell, you shouldn't need a proxy user to do any of this. You don't need separate EXECUTE AS outside the proc, you don't need multiple EXECUTE AS inside the proc, and you don't need a REVERT anywhere.
Your proc (any proc) should look like the following and you need to do a GRANT EXECUTE as I have done in the code below, preferably it would be for a DBRole ...
CREATE PROCEDURE dbo.SomeStoredProc
@SomeParam1 SOMEDATATYPE,
...
@SomeParamN SOMEDATATYPE
WITH EXECUTE AS OWNER
AS
... body of proc ...
;
GO
GRANT EXECUTE ON dbo.SomeStoredProc TO SomeUserOrLoginOrDBRole; --In otherwords, some "principal"
GO
I haven't tried this method with SQL Server authentication... only Windows Authentication but it works very well with Windows Authentication.
Of course, whatever Windows logins the users are, should be made members of the DBRole you chose. You can also GRANT EXECUTE on the proc to individual users but most folks, including me, will recommend that you use DBRoles, instead, and assign the users to the correct roles.
Whichever the case, the DBRole or individual users should only require PUBLIC privs and EXECUTE privs on the procs you want them to be able to execute. 🙂
--Jeff Moden
October 27, 2011 at 6:26 pm
Jeff, that would not be the first time I've overcomplicated my life.
Just to put aside all the mental junk, I decided to do this all from scratch. Here's the script I used:
--to be absolutely certain that we are not creating all this with sufficent server permissions:
EXECUTE AS LOGIN = 'sa';
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET ANSI_PADDING ON;
USE [master];
CREATE DATABASE DemoServerPermDatabase;
GO
Use [DemoServerPermDatabase];
GO
CREATE ROLE DBAdminRole;
CREATE LOGIN lowuser WITH PASSWORD = 'p@ssword1';
CREATE USER lowuser FOR LOGIN lowuser;
GO
CREATE PROCEDURE dbo.aspCreateUser (
@UserName sysname
) WITH EXECUTE AS OWNER AS
BEGIN
DECLARE @stmt varchar(MAX);
SET @stmt = 'USE [master]; CREATE LOGIN ' + QUOTENAME(@UserName) + ' WITH PASSWORD = ''p@ssword1'';';
EXEC (@stmt);
SET @stmt = 'USE [DemoServerPermDatabase]; CREATE USER ' + QUOTENAME(@UserName) + ' FOR LOGIN ' + QUOTENAME(@UserName) + ';';
EXEC (@stmt);
END;
GO
GRANT EXECUTE ON dbo.aspCreateUser TO [DBAdminRole];
GO
EXEC sp_addrolemember [DBAdminRole], [lowuser];
GO
EXECUTE AS LOGIN = 'lowuser';
GO
--Supposed to work...
EXECUTE DemoServerPermDatabase.dbo.aspCreateUser @UserName = 'newuser';
GO
REVERT;
USE [master];
DROP DATABASE DemoServerPermDatabase;
DROP LOGIN [lowuser];
REVERT;
This fails with the following errors:
Msg 15247, Level 16, State 1, Line 1
User does not have permission to perform this action.
Msg 15007, Level 16, State 1, Line 1
'newuser' is not a valid login or you do not have permission.
Both errors came from the dynamic SQL that was generated. Even though the database and procedure was created and owned by all-powerful 'sa', changing the database user does not appear to imply that one gains server permissions owned by the login that happened to create those procedures. The article I linked to on previous page seems to imply that IMPERSONATE + EXECUTE statement is the only way to enable CREATE LOGIN for self-administration.
Am I still missing something?
October 30, 2011 at 10:41 am
So far, I've not gotten any suggestions where I may have done it wrong and thus avoid the need to grant IMPERSONATE and manage this within a stored procedure, I'm now thinking about using DDL trigger to provide a semblance of limiting the IMPERSONATE scope to only one database and for only certain logins. At the glance, it looks feasible but since EVENTDATA doesn't say whether a T-SQL command came from a stored procedure or not, I'm not sure it'll accomplish the goal.
As always, if anyone has an example where I did it wrong or a working script or any other suggestions, I'm all eyes.
October 30, 2011 at 11:29 am
Banana-823045 (10/27/2011)
Am I still missing something?
Apparently not. I'm getting the same errors as you and haven't been able to suss it with something as simple EXECUTE AS OWNER. This particular may require a "certificate" or something else that I've not played with, yet. I believe you're doing the right thing by working in Sommarskog's article on the subject.
--Jeff Moden
October 30, 2011 at 7:02 pm
Jeff, I'm so glad you mentioned certificates. Apparently I didn't read too carefully or understood Erland's section and got fixated on TRUSTWORTHY which I still think is wrong solution. But certificates does seems to be just what I need since he has a sample of granting an user bulk copy operation via certificate within a stored procedure.
After some tweaking, I was able to use certificate to give me the permission through the stored procedure, so count me a happy camper.
That said, few things for benefit of posterity -- keeping in mind that I don't think I fully grasp the nuances of security model here.
1) It seems that this will fail:
EXECUTE AS USER = 'testuser';
EXEC dbo.aspAddUser
REVERT;
complaining that there's insufficient permissions. But this will succeed:
EXECUTE AS LOGIN = 'testuser';
EXEC dbo.aspAddUser
REVERT;
Even though the login 'testuser' is mapped to the user 'testuser', there seems to be a considerable difference whether you execute as a user or execute as a login. If we do a select from sys.user_token and sys.login_token as Erland did in his article, you can see that with EXECUTE AS USER, usage for server principals are "DENY ONLY" while EXECUTE AS LOGIN gives us "GRANT OR DENY". I'm not sure if that is significant. I think that also implies that the stored procedure shouldn't have anything for WITH EXECUTE AS ... or that'll mess up the chaining to certificate.
2) It seems that I can't just grant my certificate user the db_securityadmin role. This will fail in an permission error. OTOH, if I do grant the user ALTER ANY USER permission, the stored procedure does succeed. Maybe roles just don't get chained correctly via certificate user?
EDIT: I think we can chalk #2 up to error on the operator's part. I had incorrectly assumed db_securityadmin had ALTER ANY USER permissions but that is not the case. For the sprocs to succeed, it should either have explicit ALTER ANY USER + ALTER ANY ROLE permissions OR be member of two fixed roles; db_securityadmin and db_accessadmin.
3) The first time I built everything I needed, I got the permission errors and decided I was doing it all wrong so I took Erland's full script for bulk copy and replaced each T-SQL with what I needed so the sequential order were identical to what Erland used. With obvious exception I think it's insignificant but considering how fussy the process was, thought that I should mention trying to do it in same sequential order may at least help reduce the likelihood that the final result will fail due to a missing component.
The process is quite involved and there's the requirement to re-sign the procedure for each alternation I do but I think overall, it's well worth the efforts to properly lock down the server while enabling the users of database to have some degree of self-adminstration.
Thanks, Jeff & Lynn!
October 30, 2011 at 8:06 pm
I missed the nuance myself. Thank you for taking the time to post the feedback.
The other nuance of the Certificate login having the "ALTER ANY USER" priv also appears to be important. It would appear that I have a little studying of my own to do.
--Jeff Moden
October 31, 2011 at 3:54 am
After running into permission errors again, I've edited my previous post - I actually had made an incorrect assumption about the db_securityadmin. I thought it had the ALTER ANY USER permissions but according to this page, I'd need to also add db_accessadmin role to get that permission.
Considering that both roles together gives too much permissions, I opted to grant my certificate user only two permissions that are actually required; ALTER ANY USER and ALTER ANY ROLE, in addition to server-level permission ALTER ANY LOGIN. The sprocs then works correctly.
November 3, 2011 at 4:45 pm
I have a snag -
I have one stored procedure that is meant to return to the database admins a list of logins and whether they're active or not. That is used so that they can activate/inactivate a login that's mapped to their database.
The issue is that for some reasons, sys.server_principal won't show the list of logins unless I'm running it as the sysadmin. Figuring it'd be also solved by signing the stored procedure to the same user that has ALTER ANY LOGIN permission, I tried it and to my surprise, it would not show anything except the original login. In fact, even if I grant sysadmin role to the certificate user (Danger, Will Robinson!), the sys.server_principal still refuses to show any other logins beyond the currently-logged-in login and original login.
The fact that certificate login had sysadmin and still couldn't show any other logins suggest to me that there's more to the picture that I'm missing.
Any suggestions?
November 8, 2011 at 3:09 pm
Banana-823045
Apparently I didn't read too carefully or understood Erland's section and got fixated on TRUSTWORTHY which I still think is wrong solution. But certificates does seems to be just what I need since he has a sample of granting an user bulk copy operation via certificate within a stored procedure.
This seems to indicate another article that you have read. What you have gone through here may have application where I am working, but I seem to be missing some critical info. Anything you can provide regarding the problem you are solving here would be quite welcome, even if it is only links to other articles.
November 8, 2011 at 3:14 pm
It's the same article I linked to in my OP. As explained to Jeff, I got fixated on the wrong section.
Here's the link again: Erland's treatise.
More specifically this part:
and in case of managing server-level permissions:
I hope that helps.
November 8, 2011 at 3:24 pm
Banana-823045 (11/8/2011)
It's the same article I linked to in my OP. As explained to Jeff, I got fixated on the wrong section.Here's the link again: Erland's treatise.
More specifically this part:
and in case of managing server-level permissions:
I hope that helps.
Thanks, I didn't go back far enough in the thread. This helps consolidate things in one location.
I will let you know as I work on the process here at work. I'm sure it will help jump start the work more than me starting from scratch.
February 21, 2012 at 5:30 pm
Banana - Are you still working this issue?
I'm working on something similar to what you are doing. For my implementation I want to grant users the ability to backup the security in their Dev environment, set the DB to single user, restore the DB, and reapply the security they backed up, as well as a few other scripts. I have been successful in using the Certificate implementation for most cases. So far, my users can:
- check current activity on the server
- kill sessions on the server
- set the DB to single or multi user mode
- check for orphaned users
I'm running into an issue in granting my Certificate user access to the newly restored DB. I'm interested in where you landed up on this issue. We may be able to cross notes.
February 22, 2012 at 9:41 pm
DSquared -
I recently moved to a new server so had a need to move my certificate as well. One issue is that certificate are also encrypted using the service master key, so I had to export the service master key (I also did the same for database master key but I can't remember offhand if that was a necessary part of the process for this specific situation). Once you set up the service master key, the certificate will then work correctly.
See if this get you going.
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply