Setting different databases to use in a stored procedure
-
Hello all, I'm afraid that I'm a newbie when it comes to SQL Server and their stored procedures. I'm trying to automate setting up of logins and permissions to databases for our Accounting software. I want to set up one stored procedure for the accounting/IT guy to perform these functions:
Create a login (this part works fine)
Assign permission to access 4 databases as 'public' (this does not work fine)
So here's what I've got:
create procedure mpi_cpaddlogin
@loginame sysname, -- user's login name in syslogins
@name_in_db sysname = NULL
as
-- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
set nocount on
declare @ret int
DECLARE @cmd nvarchar(255)
-- HL -- Add user login first (puts in syslogins)
execute @ret = sp_addlogin @loginame, 'password', 'tempdb'
-- LIMIT TO SQL/NT USERS IN SYSLOGINS (BCKWRD COMPAT ONLY!)
if not exists (select * from master.dbo.syslogins where loginname = @loginame
and (isntuser = 1 or isntname = 0))
and @loginame <> 'guest'
begin
raiserror(15007,-1,-1,@loginame)
return (1)
end
if @name_in_db is null
select @name_in_db = @loginame
*********************************
-- ADD THE USER TO THE DATABASE --
SELECT @cmd = 'USE DELTEKCP'
execute @ret = sp_grantdbaccess @loginame, @name_in_db OUT
if (@ret <> 0)
return (1)
SELECT @cmd = 'USE DEVLCP'
execute @ret = sp_grantdbaccess @loginame, @name_in_db OUT
if (@ret <> 0)
return (1)
SELECT @cmd = 'USE TESTCP'
execute @ret = sp_grantdbaccess @loginame, @name_in_db OUT
if (@ret <> 0)
return (1)
SELECT @cmd = 'USE TRAINCP'
execute @ret = sp_grantdbaccess @loginame, @name_in_db OUT
if (@ret <> 0)
return (1)
*****************************************************
-- RETURN SUCCESS --
return (0) -- mpi_cpaddlogin
GO
My problem exists between the asterisks. the line: execute @ret = sp_grantdbaccess @loginame, @name_in_db OUT works just fine for whatever database I'm running the stored procedure from (in my cast, master) but I want to specify the four other databases to grant the access to, not master.
Any help would be greatly appreciated. Thanks!
Hollis
-
Why not have an NT group created that has the permissions already established for it in SQL and then as people come and go add/remove them from the group via NT security and BOOM done. 'nuff said
Good Hunting!AJ Ahrens
webmaster@kritter.net -
AJ
That thought occurred to me, but I don't think that will work with the accounting software. It actually uses the Database Login to login to the accounting software. So they HAVE to have a database login in SQL Server to work properly. Thanks for the suggestion! Any other ideas?
Hollis
-
Instead of this:
SELECT @cmd = 'USE DELTEKCP'
execute @ret = sp_grantdbaccess @loginame, @name_in_db OUT
Try this:
set @cmd = 'USE DELTEKCP sp_grantdbaccess ' + @loginame + ', ' + @name_in_db + 'OUT'
exec @ret = sp_executesql @cmd
and similarly for the other dbs.
BTW, BOL recommends that you use SET rather than SELECT when assigning the values of local variables.
Regards
Phil
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Can you explain why set is better than select.
I've already run tests and the speed is exactly the same.
The only difference is when you use :
Select @v1 = 1, @v2 = 2, @v3 = 3
instead of :
SET @v1 = 1
SET @v2 = 2
SET @v3 = 3
where the select form will be considerably faster than the 3 set (in a pretty big while loop).
-
Phil,
I tried what you suggested, but it is giving me an 'incorrect syntax near 'sp_grantdbaccess'. on the first line there: (set @cmd = 'USE DELTEKCP sp_grantdbaccess ' + @loginame + ', ' + @name_in_db + 'OUT'
)
Now, if I run these seperately in Query Analyzer, I'd have to run them like this:
USE DELTEKCP
GO
sp_grantdbaccess 'j_smith', 'j_smith'
Is there something in that set @cmd line that is needed like the GO is needed in Query Analyzer?
Thanks for your reply!
Hollis
-
I would think so.. try this
set @cmd = 'USE DELTEKCP' + char(13) + char(10) + 'GO' + char(13) + char(10) + 'sp_grantdbaccess ' + @loginame + ', ' + @name_in_db + 'OUT'
-
Remi,
Ok, that didn't work either but it gave me another error that gave me some good information. I got "Error 170. a USE database statement is not allowed in a procedure or trigger." So I guess that puts a nix on what I was trying to do. So I guess I'll broaden my question a little.
I want to be able to run one stored procedure (if possible, if there are any different ways to do this I'm open for those) that can create a login like using sp_addlogin. Then I want to grant access to that login to 4 different databases. (just like me opening up the login in Enterprise Manager, going to the Database Access tab, and selecting Permit for those 4 databases). Does anyone have any ideas?
Thanks!
Hollis
-
The dynamic sql gets around the limitation of using "USE" in a stored procedure. The problem with Phil's code was that a stored procedure cannot be called using only the name of the procedure if it is not the first statement in the batch. In this case, the USE statement is first, and sp_grantdbaccess is 2nd. So, either a "GO" must be used, or "EXEC" to execute the procedure. Try this -
set @cmd = 'USE DELTEKCP EXEC sp_grantdbaccess ' + @loginame + ', ' + @name_in_db + 'OUT'
exec @ret = sp_executesql @cmd
--Steve
-
Steve! Sweet!
It works! That's exactly what I needed. It works like a charm.
Thank you everyone for your help!
Hollis
-
I can't explain it, but here is a quote from BOL:
SELECT @local_variable
Specifies that the given local variable (created using DECLARE @local_variable) should be set to the specified expression.
It is recommended that SET @local_variable be used for variable assignment rather than SELECT @local_variable. For more information, see SET @local_variable.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
SET is better than SELECT for a couple of different reasons
- It doesn't return anything to the caller so it doesn't have to have "presentation" code
- Runs faster because of #1
- Don't have to worry about multiple "recordsets" possibly being returned to calling programs i.e. VB.NET, etc...
- Use less memory because of the above reasons
- I'm sure there are others but, I'm getting tired......
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Just a little suggestion to the topic. Why not use in dynamic SQL just fully qualified name, like this:
set @cmd = 'DELTEKCP..sp_grantdbaccess ' + @loginame + ', ' + @name_in_db + 'OUT'
exec @ret = sp_executesql @cmd
-
I just made a little search on my previous question and here's the best link I found.
http://vyaskn.tripod.com/differences_between_set_and_select.htm
And I'm sorry to contradict you Aj but none of your 5 points were listed in any of the articles I read.
To resume, the only 4 major differences are this :
1 - SET is ainsi standard while select is not.
2 - SET cannot trap the error and rowcount value in the same statement without useless gymnastics... this code is the simplest to use : select @rowcnt = @@rowcount, @err = @@error
3 - Select will always be faster if you do multiple assignations at once (like the one above (any version and processor speed/qte))
4 - Select will return the last row found instead of an error in a statement like this : SELECT @j-2 = j FROM #Test WHERE i = 1
-
Svetlana,
Thanks for the post! I tested it and it worked just fine!
This is great learning for me as I can see how different things work. Thank you!Hollis
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply