USE keyword in a SP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

USE keyword in a SP

Author

Topic

allan_houston
Starting Member

18 Posts

Posted - 2006-07-06 : 16:47:37

Hello everyone

I'm a noob in t-sql but I've been programming in some OO languages.

On a sql server 2000 am I supposed to create a procedure that will create a login, a database and a user in that database that will have full permission on that database:)

This is what I created so far:

create procedure addNewUser @login varchar(30) as
if db_id(@login) > 0
print 'database ' + @login + ' exists'
else
begin
exec sp_addlogin @login
exec('create database ' + @login)
exec('USE ' + @login) -- NOT FIREING
exec sp_adduser @login, @login, 'db_owner'
end

I understanda that create database can not use a variable. Thats why I put is like that. I also understand that USE can not be used inside a procedure or function. Is there any other way of creating a user in the new database then.

My other question is how can I see what statements can not use variables like "create database"-statement?

I appreciate any answer that you can come up with
Thx

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-06 : 17:02:54

You will need to use dynamic SQL for the CREATE DATABASE part. Check out the dynamic SQL article:
http://www.sqlteam.com/item.asp?ItemID=4599

You can use DatabaseName.dbo.sp_adduser rather than the USE statement. If DatabaseName is not known yet, then you'll need to use dynamic SQL again.

Tara Kizer
aka tduggan

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-07-06 : 17:02:59

Put the USE database_name and exec sp_adduser statements inside a single varchar string, and execute that with the EXECUTE statment.

CODO ERGO SUM

allan_houston
Starting Member

18 Posts

Posted - 2006-07-07 : 03:23:25

>You can use DatabaseName.dbo.sp_adduser rather than the USE >statement. If DatabaseName is not known yet, then you'll need to >use dynamic SQL again.

sp_adduser lies in master DB only, right?!

exec('USE ' +@DB) will never get you to you destination DB

This is what I built but it madde nina be a db_owner of the master DB becouse I activated it there

declare @thisUser char(50)
set @thisUser = 'USE ' + @login
set @thisUser = ' GO '
--set @thisUser = 'sp_adduser ''' + @login + ''', ''' + @login + ''', ''db_owner'''
exec(@thisUser)

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-07-07 : 03:34:08

if you mean grantdbaccess and addrolemember

set @sql='use ' + @databasename + '; exec sp_grantdbaccess ''' +
@loginame + ''',''' + @loginame + ''';exec sp_addrolemember ''db_owner'',''' + @loginame + ''''

--------------------
keeping it simple...

mr_mist
Grunnio

1870 Posts

Posted - 2006-07-07 : 04:23:26

Firstly, the style of command where you do

exec USE + some variable
exec somecommand

Will not work, because the context for the USE command is seperate from that of the executed somecommand. You'd have to put the stuff into one line, as Jen has shown.

Secondly, using sp_adduser is deprecated. You should use sp_grantdbaccess (for 2000) or CREATE USER (for 2005).

Thirdly, having a procedure that does this is a bad idea, but assuming you're going to go ahead with it anyway -


declare @person sysname 
declare @dbname sysname
declare @mysql nvarchar (255)

set @dbname = 'northwind'
set @person = 'moo'
exec sp_addlogin @loginame = @person, @defdb = @dbname -- note lack of password is entirely insecure

set @mysql = N'exec ' + @dbname + '.dbo.sp_grantdbaccess @loginame = ''' + @person + ''''
exec sp_executesql @mysql 

set @mysql = N'exec ' + @dbname + '.dbo.sp_addrolemember @rolename = ''db_owner'', @membername = ''' + @person + ''''
exec sp_executesql @mysql

You don't have to USE the database at all, you can execute sp_ procedures from master in the context of whatever database you like.

-------
Moo. :)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-07 : 11:21:07

quote:
Originally posted by allan_houston

sp_adduser lies in master DB only, right?!

Did you even try DatabaseName.dbo.sp_adduser? Yes it is in the master database, but that is how you can use it in your user database. Please try it at least.

Tara Kizer
aka tduggan

allan_houston
Starting Member

18 Posts

Posted - 2006-07-08 : 13:35:22

Hello

Thx for your help. Id worked when I put it in a single sting.

To tkizer: sp_adduser SP does not exist in my database. Of course I tried. Why would I say that it didn't work if I didn't try.
Thx anyway:)

allan_houston
Starting Member

18 Posts

Posted - 2006-07-08 : 13:36:49

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-08 : 21:29:07

quote:
Originally posted by allan_houston

Hello

Thx for your help. Id worked when I put it in a single sting.

To tkizer: sp_adduser SP does not exist in my database. Of course I tried. Why would I say that it didn't work if I didn't try.
Thx anyway:)

I know it doesn't exist outside of the master database. But it does work when you want to add a user to a user database. It really does work. I even tested it prior to posting it in this thread.

Tara Kizer
aka tduggan

allan_houston
Starting Member

18 Posts

Posted - 2006-07-09 : 07:45:21

Hello tkizer

I have to apologize on behalf of me and my co-workers. Our DBA did something that prevented us of using sp_adduser in DB other than master. Now we are able to use it as you described.

Pls except our apologize.

Thx for your time and help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources