April 9, 2009 at 10:32 am
How can i create a SP for a non sysadmin which will allow the user to create a database and his login id will be assgiend to db_writer role.
This proc should also check for database name if already exists in 4 servers (Rev1,Dev1,Rev2,Prod1).
I also doubt if need to create this proc in a specofic database or it has to be in MAster to make it work.
April 9, 2009 at 10:44 am
CREATE PROCEDURE CreateNewClientDatabase
@DBName varchar (128),
@Username varchar (30)
WITH EXECUTE AS 'dbo'
AS
if not exists(select dbid from master.sysdatabases where name = @DBName)
CREATE DATABASE @DBName
print 'Database @DBName Created'
else
raiserror("Database already exists.",3)
CREATE USER [@Username] FOR LOGIN [@Username]
EXEC sp_addrolemember N'db_datareader', N'@Username'
if @@error = 0
return 0
else
return 1
END
April 9, 2009 at 10:54 am
Mike Levan (4/9/2009)
CREATE PROCEDURE CreateNewClientDatabase
@DBName varchar (128),
@Username varchar (30)
WITH EXECUTE AS 'dbo'
AS
if not exists(select dbid from master.sysdatabases where name = @DBName)
CREATE DATABASE @DBName
print 'Database @DBName Created'
else
raiserror("Database already exists.",3)
CREATE USER [@Username] FOR LOGIN [@Username]
EXEC sp_addrolemember N'db_datareader', N'@Username'
if @@error = 0
return 0
else
return 1
END
Actually, this won't work. You need to use dynamic sql to do the CREATE DATABASE and the CREATE USER statements. There is a problem with the RAISERROR statement, besides using double quotes, that I am looking into. Also the call to sp_addrolemember is incorrect.
Give me some time and I will see if I can get this rewritten so that you can give it a try, barring of course someone else beating me to the rewrite or providing another solution.
April 9, 2009 at 11:04 am
The only thing I haven't done yet is actually test the procedure, but the following code will actually create the procedure.
CREATE PROCEDURE dbo.CreateNewClientDatabase (
@DBName varchar (128),
@Username varchar (30)
)
WITH EXECUTE AS 'dbo'
AS
BEGIN
declare @SQLCmd varchar(max);
if not exists(select database_id from master.sys.databases where name = @DBName)
begin
set @SQLCmd = 'CREATE DATABASE ' + @DBName + ';';
exec (@SQLCmd);
print 'Database @DBName Created';
set @SQLCmd = 'CREATE USER ' + @Username + ' FOR LOGIN ' + @Username + ';';
EXEC sp_addrolemember N'db_datawriter', @Username;
return 0
end
else
begin
raiserror('Database already exists.',10,1);
return 1;
end
END
I am going to test it in a bit, but I need to setup a non-privledged user first, and I have other things i need to do at the moment as well.
If you can, please give the code a spin as well and let us know if there are any problems.
April 9, 2009 at 11:14 am
********* WARNING ***********
A quick aside regarding my code. It needs more work. Currently it is wide open to SQL Injection. This would be a good opportunity for you to read more about it and see if you can modify the code to protect your system from this type of attack.
April 9, 2009 at 11:17 am
don't you have to add a USE DATABASENAME command before you add the user, otherwise it would just re-add the user to the existing connected database?
Lowell
April 9, 2009 at 11:20 am
I think Tara wants to check dbname from 4 different servers and more over she dont need to give create command, she can directly create database as it is being executed by dbo.
Tara
I think thi store proc has to be created in a master db instead of specific then only it works.
pardon me if i am mistaken.
April 9, 2009 at 11:30 am
Lowell (4/9/2009)
don't you have to add a USE DATABASENAME command before you add the user, otherwise it would just re-add the user to the existing connected database?
Probably. Work got in the way of further testing. Now that I'm getting ready to have lunch, I can see what other changes are needed bsided protecting the proc from SQL Injection.
April 9, 2009 at 11:31 am
thank you guys..
Yes as Mike said, I have to check for db name on all 4 servers.
I have to hook this proc to an app where i have to allow Users to create db and also they have to get permissions to write on that db.
April 9, 2009 at 11:38 am
Mike Levan (4/9/2009)
I think Tara wants to check dbname from 4 different servers and more over she dont need to give create command, she can directly create database as it is being executed by dbo.Tara
I think thi store proc has to be created in a master db instead of specific then only it works.
pardon me if i am mistaken.
Well, first of all, your code wouldn't work at all. Second, the procedure will most likely need to be run separately on each of the specified servers one at a time.
Now, I need to do some testing. Something I think you should have done as well. At least my code will actually create the proposed stored proc.
April 9, 2009 at 11:43 am
Lynn I aggree with you, may code has errors.
that was the rough idea i gave to Tara.
Lynn
When you said she has to run code on each server instead can she select list of database from all 4 servers and find if th new db name is in there?
something like using union to list all the databases from 4 servers.
select database_id from Rev1.master.sys.databases where name = @DBName
union
select database_id from Rev2.master.sys.databases where name = @DBName
union
select database_id from Prod1.master.sys.databases where name = @DBName
April 9, 2009 at 11:53 am
This may take more work. Even under a privledged account, I get an error with the CREATE DATABASE part of the stored proc.
Don't give up hope yet. If I can't come up with it, I'm sure there is someone out there that may come up with a solution.
Since you mention that this needs to be done from an application, it may be necessary to write something using SMO to accomplish this task on multiple servers.
April 9, 2009 at 11:57 am
Lynn
May be you are missing exec (@SQLCmd); after creating a user.
April 9, 2009 at 12:00 pm
Nope. Permission issue.
April 9, 2009 at 12:03 pm
here's my version, where i figured i'd find and replace from an existing set of commands i know works fine:
i greated a login named 'bob' with connect priviledges. this created the database, but fails when doing the rest...adding roles and users, then users to roles.
but i get an error stating this:
Msg 916, Level 14, State 1, Line 2
The server principal "sa" is not able to access the database "Special" under the current security context.
ALTER procedure 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.';
RETURN 1;
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
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]
--create role for my normal users
CREATE ROLE [WhateverUsers]
ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [WhateverUsers]
ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [WhateverUsers]
CREATE USER [MyDBUser] FOR LOGIN [[MyDBUser]]
EXEC sp_addrolemember N''[WhateverUsers]'', N''MyDBUser''';
If @WithDevPriviledges <> 0
BEGIN
SET @SQLCmd= @SQLCmd + '
EXEC sp_addrolemember N''[WhateverAdmins]'', N''MyDBUser''';
END
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
MakeMeADatabase 'Special','bob'
Lowell
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply