May 25, 2011 at 2:01 pm
We have an application that creates, deletes, takes ownership, and attaches/detaches multiple databases. All of this is possible if the login has the sysadmin role.
Due to new policy restrictions, the login can no longer have the sysadmin role.
What can be done with other server roles and/or database user/roles to allow this functionality to continue without sysadmin?
Is this even possible?
May 25, 2011 at 2:05 pm
grant it dbcreator role, and for databases not created under the security of that ID add it to the db_owner role
---------------------------------------------------------------------
May 25, 2011 at 2:18 pm
I've created a stored procedure featuring EXECUTE AS that allows an end user to Create a Database, which then adds roles to that database, and finally adds the calling user as a user in those insta-created roles.
you could create procs to do the commands that used to require sysadmin privileges, and have the app call those commands instead .
Lowell
May 25, 2011 at 3:37 pm
Thanks for the reply George and Lowell.
With the dbcreator role, I was able to create and access the newly created database.
Also, I was able to attach a database that I didn't create. However, I was not able to access it or
exec sp_addrolemember db_owner,[DBuser] due to permissions.
So this is what I did:
1. I have a login with sysadmin role [SAlogin]
2. I have a login with dbcreator role [DBlogin]
3. My database user [DBuser] is mapped to [DBlogin]
4. GRANT IMPERSONATE ON LOGIN:: [SAlogin] TO [DBlogin]
5. Once I attach an existing database [XYZ], execute the following as [DBlogin]:
EXECUTE AS LOGIN = 'SAlogin'
go
use [XYZ]
CREATE USER [DBuser] FOR LOGIN [DBlogin] WITH DEFAULT_SCHEMA=[dbo]
go
sp_addrolemember db_owner,[DBuser]
use [master]
revert
go
This process appears to work correctly for my needs.
To summarize, this requires a login with sysadmin role to be impersonated and a database user to be added as db_owner.
Is this the general approach to how security is controlled within SQL Server?
Can this be accomplished without sysadmin impersonation?
The reason I ask is just in case the security admin says sysadmin impersonation is not allowed.
Andrew
May 25, 2011 at 4:48 pm
the OP asked in a PM how I had done my stored proc i referenced; I'm posting it here to add fuel to the fire; others might have some ideas , enhancements, or better strategies than what I had done:
---i had to do this to my master database on my server:
---ALTER DATABASE master SET TRUSTWORTHY ON;
ALTER procedure sp_MakeMeADatabase(@dbname varchar(128),@UserName varchar(128),@WithDevPriviledges int = 0)
WITH EXECUTE AS 'dbo'
AS
DECLARE @SQLCmd varchar(max)
BEGIN
IF EXISTS(SELECT name FROM master.dbo.sysdatabases WHERE name = @dbname)
BEGIN
PRINT 'Database Already Exists,No Need To Create.';
END
ELSE
BEGIN
PRINT 'Creating Database.';
set @SQLCmd = 'CREATE DATABASE ' + @DBName + ';';
exec (@SQLCmd);
END
SET @SQLCmd='
USE Whatever
--create the Role for my Dev guys
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N''WhateverAdmins'' AND type = ''R'')
BEGIN
CREATE ROLE [WhateverAdmins]
ALTER AUTHORIZATION ON SCHEMA::[db_ddladmin] TO [WhateverAdmins]
ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [WhateverAdmins]
ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [WhateverAdmins]
END
--create role for my normal users
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N''WhateverUsers'' AND type = ''R'')
BEGIN
CREATE ROLE [WhateverUsers]
ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [WhateverUsers]
ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [WhateverUsers]
END
--create the user if it does not exist yet
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N''MyDBUser'')
CREATE USER [MyDBUser] FOR LOGIN [MyDBUser]
--add the user to the role
EXEC sp_addrolemember N''WhateverUsers'', N''MyDBUser''';
If @WithDevPriviledges <> 0
BEGIN
SET @SQLCmd= @SQLCmd + '
EXEC sp_addrolemember N''WhateverAdmins'', N''MyDBUser''';
END
--change the default sql to have the specific database and user in question
SET @SQLCmd=Replace(@SQLCmd,'Whatever',@DBName);
SET @SQLCmd=Replace(@SQLCmd,'MyDBUser',@UserName);
PRINT @SQLCmd;
EXEC (@SQLCmd); ----two roles should be in place now
END
GO
exec MakeMeADatabase 'Special','bob'
exec MakeMeADatabase 'Special','jeff',1
exec MakeMeADatabase 'Special',user_name(),1
Lowell
May 26, 2011 at 4:44 am
I'd use lowells stored proc method with execute as to grant permissions in the database rather than impersonate a login with sysadmin rights.
If other accounts than dblogin are CREATING databases and you wish dblogin to have rights in those databases (and every new database going forward), you could consider adding dblogin to the model database.
---------------------------------------------------------------------
May 26, 2011 at 7:52 pm
I used Lowell's procedure and it worked as advertised.
However, it does require creating the procedure in the master database AND setting TRUSTWORTHY ON.
Is this correct?
I was not able to get it to work if the proc was in a database that is owned by a non-sysadmin account.
Is this possible, am I missing something?
May 26, 2011 at 8:20 pm
I have to admit, this is all a bit odd. The app, which I assume has been thoroughly tested to run correctly and safely, can't use SA but you're allowed to use SA using Impersonation and other tricks in an untested and relatively unsafe manner? The person who made the decision on the app needs a nice, high velocity, pork chop dinner with all the fixin's. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
May 26, 2011 at 8:21 pm
i'm very weak on the reason why i had to do that trustowrthy thing;
can you turn off trustowrthy, change the proc to WITH EXECUTE AS SELF, and see if that works?
SELF is the user who created the proc, probably a sysadmin, right?
maybe that resolves the trustworthyness issue; i'm not at a PC with SQL right now so i cannot test, but that was my first thought;
i wrote that example a long while ago,.
Lowell
May 27, 2011 at 12:35 pm
Yes, it is odd. If there is anyway to keep the functionality in the app without requiring SA, we can update the code.
SA impersonation may not be acceptable either, but I wanted to investigate to provide sufficient explanation for using it.
Lowell, I did as you suggested and this is what I got back:
Msg 916, Level 14, State 1, Line 2
The server principal "sa" is not able to access the database "Test8" under the current security context.
My guess is ownership chaining limitations?
Thanks for your help.
Andrew
June 15, 2011 at 7:08 pm
Lowell (5/26/2011)
i'm very weak on the reason why i had to do that trustowrthy thing;can you turn off trustowrthy, change the proc to WITH EXECUTE AS SELF, and see if that works?
SELF is the user who created the proc, probably a sysadmin, right?
maybe that resolves the trustworthyness issue; i'm not at a PC with SQL right now so i cannot test, but that was my first thought;
i wrote that example a long while ago,.
WITH EXECUTE AS SELF is the same as not having a WITH EXECUTE. WITH EXECUTE AS OWNER is what I believe you're looking for. There is a possibly huge problem with that, though... if you have tables with a "Created By" or "Last Modified By" column any you rely on things like SUSER_NAME() to populate them, it will no longer identify the correct user running the sporoc... it will always show up as the owner of the proc, instead.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply