October 3, 2012 at 2:04 am
hello,
I have a login on sql server with puplic access and I want it to create users in other databases without giving it sysadmin priveledges.
I want it to be part of master database only but have the ability to create users in other databases.
Any help is appreciated
Regards,
T
October 3, 2012 at 2:10 am
You will need to grant the server role securityadmin, which will allow the user to create logins at the server level and then create users in the database level based on that login.
October 3, 2012 at 2:24 am
hello,
I tried it but not working.
Basically I have a SP that checks for a user in the user databases and if it is not there it creates it with db_owner membership.
I want a particular user to be able to run this SP from master DB without giving that particular user any elevated previledges like sysadmin .
ALTER PROCEDURE [dbo].[User_addition_to_db]
WITH EXECUTE AS caller
AS
SET NOCOUNT ON;
DECLARE @Dbname SYSNAME
SELECT [Name] INTO #temp
FROM sys.sysdatabases
WHERE DBID not in (1,2,3,4)
AND [name]<>'distribution'
WHILE (SELECT COUNT(*) FROM #temp)>0
BEGIN
SELECT TOP 1 @Dbname=[Name] FROM #temp
--
DECLARE @sql VARCHAR(max)
SET @sql=N'USE ['+@Dbname +']ALTER DATABASE '+ @DbName+' SET TRUSTWORTHY ON;
IF EXISTS(SELECT name FROM master.sys.server_principals WHERE name = ''XXXX'' AND type = ''S'')
AND NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N''YYYY'')
CREATE USER [YYYY] FOR LOGIN [XXXX];
EXEC sp_addrolemember N''db_owner'', N''YYYY'';'
EXECUTE (@sql)
DELETE FROM #temp WHERE [name]=@dbname
END
DROP TABLE #temp
IF EXISTS(SELECT name FROM sys.database_principals WHERE name = N'YYYY')
PRINT 'USER EXISTS'
October 3, 2012 at 2:32 am
Execute as caller wont work, you will need to elevate the permissions to an account which has access unless you want to grant the caller the access they need to create accounts?
October 3, 2012 at 2:47 am
That is what i need.
I want the caller(particular user/goofy) to be able execute this SP.
I have created a sql login [goofy] with securityadmin previledge.When i log in as goofy and try to run the SP i get below error:
The server principal "goofy" is not able to access the database "POPOP" under the current security context.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply