Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Minimum rights required to add users to a DB Expand / Collapse
Author
Message
Posted Tuesday, July 9, 2013 9:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 10:44 AM
Points: 7, Visits: 29
Hi All

We run both SQL 2008/2012 but this question stands for both versions.
I would like to delegate permissions to our helpdesk so that they can add new (SQL authenticated) user accounts to a particular DB then assign the db_datareader & db_datawriter roles to the account.

So, in Management Studio I have created a SQL login for the Helpdesk AD group and assigned the securityadmin server role, then gone to the relevant DB and assigned the db_securityadmin and db_access admin roles for this group.

Problem is that when the Helpdesk try to create a new user for this DB they can create a server login but get the following error when assigning DB roles to that user:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Add member failed for DatabaseRole 'db_datareader'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.2100.60+((SQL11_RTM).120210-1917+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Add+member+DatabaseRole&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------


I cancel out of the message only to find that the server login has been created for the new user but not the DB assignments.

The only way I have found to get around this is to assign the Helpdesk group to DBO DB role but this is just too permissive for them. Surely there is a way to do this without assign DBO for the Helpdesk??

Thanks in advance
Post #1471700
Posted Tuesday, July 9, 2013 9:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:06 AM
Points: 12,905, Visits: 32,179
how about creating a procedure that runs under elevated privilesges, and give the helpdesk permissions to that?

here's just one example:
CREATE procedure pr_CallBoostedSecurityProcess( @name varchar(128), @password varchar(128) )
WITH EXECUTE AS 'superman'
--this is a sysadmin login and also database user
--(only database users can be used with EXECUTE AS clause in a proc),
--which has been disabled, but is used for elevated permissions.
AS
BEGIN
--CREATE a login, if required.
DECLARE @cmd varchar(max)
IF NOT EXISTS(SELECT 1 FROM master.sys.server_principals WHERE name = @name)
BEGIN
SELECT @cmd = 'CREATE LOGIN ' + QUOTENAME(@name)
+ ' WITH PASSWORD = ''' + @password +''','
+ 'N'' DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON;'
PRINT @cmd
EXECUTE(@cmd)
END --IF
--Create Our role with specific permissions, if required.
IF NOT EXISTS(SELECT * FROM sys.database_principals WHERE type_desc = 'DATABASE_ROLE' and name = 'MyStandardPermissions')
BEGIN
SELECT @cmd = 'CREATE ROLE MyStandardPermissions;'
PRINT @cmd
EXECUTE(@cmd)

SELECT @cmd ='EXEC sp_addrolemember N''db_datareader'', N''MyStandardPermissions'';'
PRINT @cmd
EXECUTE(@cmd)

SELECT @cmd ='EXEC sp_addrolemember N''db_datawriter'', N''MyStandardPermissions'';'
PRINT @cmd
EXECUTE(@cmd)
END --IF
--Create our user, if required
IF NOT EXISTS(SELECT * FROM sys.database_principals WHERE type_desc = 'SQL_USER' and name = @name)
BEGIN
SELECT @cmd = 'CREATE USER ' + QUOTENAME(@name) + ' FOR LOGIN ' + QUOTENAME(@name) + ';'
PRINT @cmd
EXECUTE(@cmd)
--assign the role to our user
SELECT @cmd ='EXEC sp_addrolemember N''MyStandardPermissions'', N''' + @name + ''';'
PRINT @cmd
EXECUTE(@cmd)
END --IF
END --PROC



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1471719
Posted Tuesday, July 9, 2013 9:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 10:44 AM
Points: 7, Visits: 29
Thanks for the reply, I'm not too hot on T-SQL but will read through it and see if I can work it out
Post #1471724
Posted Wednesday, July 10, 2013 7:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 10:44 AM
Points: 7, Visits: 29
OK, I've tweaked the elevated perms SP script as follows:

USE [SM_Test]
GO
/****** Object: StoredProcedure [dbo].[pr_CallBoostedSecurityProcess] Script Date: 10/07/2013 14:27:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[pr_CallBoostedSecurityProcess]( @name varchar(20), @password varchar(10) )
WITH EXECUTE AS 'Obewan'
--this is a sysadmin login and also database user
--(only database users can be used with EXECUTE AS clause in a proc),
--which has been disabled, but is used for elevated permissions.
AS
BEGIN
--CREATE a login, if required.
DECLARE @cmd varchar(max)
IF NOT EXISTS(SELECT 1 FROM master.sys.server_principals WHERE name = @name)
BEGIN
SELECT @cmd = 'CREATE LOGIN ' + QUOTENAME(@name)
+ ' WITH PASSWORD = ''' + @password +''','
+ ' DEFAULT_DATABASE=[SM_Test], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;'
PRINT @cmd
EXECUTE(@cmd)
END --IF
--Create Our role with specific permissions, if required.
IF NOT EXISTS(SELECT * FROM sys.database_principals WHERE type_desc = 'DATABASE_ROLE' and name = 'MyStandardPermissions')
BEGIN
SELECT @cmd = 'CREATE ROLE MyStandardPermissions;'
PRINT @cmd
EXECUTE(@cmd)

SELECT @cmd ='EXEC sp_addrolemember N''db_datareader'', N''MyStandardPermissions'';'
PRINT @cmd
EXECUTE(@cmd)

SELECT @cmd ='EXEC sp_addrolemember N''db_datawriter'', N''MyStandardPermissions'';'
PRINT @cmd
EXECUTE(@cmd)
END --IF
--Create our user, if required
IF NOT EXISTS(SELECT * FROM sys.database_principals WHERE type_desc = 'SQL_USER' and name = @name)
BEGIN
SELECT @cmd = 'CREATE USER ' + QUOTENAME(@name) + ' FOR LOGIN ' + QUOTENAME(@name) + ';'
PRINT @cmd
EXECUTE(@cmd)
--assign the role to our user
SELECT @cmd ='EXEC sp_addrolemember N''MyStandardPermissions'', N''' + @name + ''';'
PRINT @cmd
EXECUTE(@cmd)
END --IF
END --PROC




I've created the SQL login 'obewan' and added it to the sysadmin server role, but when I run the SP I get the following error:

CREATE LOGIN [test1] WITH PASSWORD = 'test', DEFAULT_DATABASE=[SM_Test], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
Msg 15247, Level 16, State 1, Line 1
User does not have permission to perform this action.
CREATE USER [test1] FOR LOGIN [test1];
Msg 15007, Level 16, State 1, Line 1
'test1' is not a valid login or you do not have permission.
EXEC sp_addrolemember N'MyStandardPermissions', N'test1';
Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 75
User or role 'test1' does not exist in this database.




Obewan has also been granted explicit perms to run this SP but should not need it due to it being a sysadmin anyway??

Any ideas folks?
Post #1472129
Posted Wednesday, July 10, 2013 7:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:06 AM
Points: 12,905, Visits: 32,179
because your obiwan user is going to be creating logins and also executing AS., it needs to be a sysadmin(for CREATE LOGIN) and db owner in the database (for CREATE USER)

this is exactly how i would script my super user in this case, and i'm assuming the database name here is [Sandbox], where yours is most likely something else:


--create our super user
CREATE LOGIN [Obewan] WITH PASSWORD=N'NotARealPassword',
DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON;
GO
--make our special user a sysadmin
EXEC master..sp_addsrvrolemember @loginame = N'Obewan', @rolename = N'sysadmin';
GO
--noone will ever login with this, it's used for EXECUTE AS, so disable the login.
ALTER LOGIN [Obewan] DISABLE;
GO
USE [SandBox];
GO
CREATE USER [Obewan] FOR LOGIN [Obewan];
GO
USE [SandBox];
GO
EXEC sp_addrolemember N'db_owner', N'Obewan';
GO



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1472154
Posted Wednesday, July 10, 2013 8:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 10:44 AM
Points: 7, Visits: 29
Apologies, I forgot to add that Obewan is part of the DB_owner role for the DB, and is a sysadmin as server level.

So do you need to grant explicit perms for CREATE USER , etc or should the role membership be sufficient? (just trying to better my understanding)

I'll give the new script a go thanks - very much appreciated
Post #1472166
Posted Wednesday, July 10, 2013 9:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 10:44 AM
Points: 7, Visits: 29
Have created the superuser but get the following error now

CREATE LOGIN [test1] WITH PASSWORD = 'password',N' DEFAULT_DATABASE=[Master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ' DEFAULT_DATABASE=[Master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ' DEFAULT_DATABASE=[Master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;'.
CREATE USER [test1] FOR LOGIN [test1];
Msg 15007, Level 16, State 1, Line 1
'test1' is not a valid login or you do not have permission.
EXEC sp_addrolemember N'VFF_Permissions', N'test1';
Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 75
User or role 'test1' does not exist in this database.


The above refers to the below and I can correct the syntax error if I remove the 'N', but then still get the permissions error I was getting previously. It shouldn't be this difficult!

SELECT @cmd = 'CREATE LOGIN ' + QUOTENAME(@name)
+ ' WITH PASSWORD = ''' + @password +''','
+ 'N'' DEFAULT_DATABASE=[Master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;'
Post #1472193
Posted Wednesday, July 10, 2013 9:17 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:06 AM
Points: 12,905, Visits: 32,179
lloks like the string is constructed slightly wrong: thank goodness for the print statements!

CREATE LOGIN [test1] WITH PASSWORD = 'password',N' DEFAULT_DATABASE=[Master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;


i think the cod eneeds to be changed like this:
SELECT @cmd = 'CREATE LOGIN ' + QUOTENAME(@name) 
+ ' WITH PASSWORD = N'''
+ @password
+ ''',DEFAULT_DATABASE=[Master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;'



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1472196
Posted Wednesday, July 10, 2013 9:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 10:44 AM
Points: 7, Visits: 29
Cool, that's fixed the syntax error but not the permissions error:

CREATE LOGIN [test1] WITH PASSWORD = N'wibble',DEFAULT_DATABASE=[Master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
Msg 15247, Level 16, State 1, Line 1
User does not have permission to perform this action.
CREATE USER [test1] FOR LOGIN [test1];
Msg 15007, Level 16, State 1, Line 1
'test1' is not a valid login or you do not have permission.
EXEC sp_addrolemember N'VFF_Permissions', N'test1';
Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 75
User or role 'test1' does not exist in this database.


This is despite creating the sysadmin superuser using the previous script, do I need explicit grant permission on the SP itself?
Post #1472216
Posted Wednesday, July 10, 2013 10:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 10:44 AM
Points: 7, Visits: 29
As a further update to this, I can enable the sysadmin account then logon and create server logins/db users manually, but not via the stored procedure it would seem.
I've also granted explicit execute permissions to the SP but am sure that's an uncessary step, but worthy doing as a troubleshooting step.
Post #1472247
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse