February 28, 2006 at 11:49 am
Hello guys.
I'm currently preparing a daily DB restore (data warehousing).
All the restore process is made from a single SP called from some other DB.
All has gone ok, but I came into a problem when I try to add some database users, since sp_grantdbaccess and sp_addrolemember work on the current db... and USE statements are not allowed in SPs.
How can programatically add users from another DB?
Thanks
February 28, 2006 at 12:21 pm
Have you tried 'fully qualifying' the name?
databasename.owner.table
northwind.dbo.customers
-SQLBill
February 28, 2006 at 12:34 pm
But I'm adding a user, it's not an object.
EXEC importeddb.dbo.sp_grantdbaccess 'user'
Won't work since sp_grantdbaccess is a procedure stored in master db.
February 28, 2006 at 1:07 pm
System stored procedures do follow the same execution convention as user stored procedures so that the SQL batch "EXEC importeddb.dbo.sp_grantdbaccess 'user'" will actualy run versus the importeddb not versus the current database.
Test batch:
use master
go
exec sp_addlogin @loginame = 'MyTestLogin' , @passwd = 'password'
go
exec NorthWind.dbo.sp_grantdbaccess @loginame = 'MyTestLogin'
go
exec sp_helpuser @name_in_db = 'MyTestLogin'
go
exec NorthWind.dbo.sp_helpuser @name_in_db = 'MyTestLogin'
go
SQL = Scarcely Qualifies as a Language
February 28, 2006 at 3:21 pm
OMG! That's true.
I had never believed that could be true.
Thank you both!
February 28, 2006 at 6:10 pm
I missed "NOT" in my statement.
Should be:
System stored procedures do NOT follow the same execution convention as user stored procedures so that the SQL batch "EXEC importeddb.dbo.sp_grantdbaccess 'user'" will actualy run versus the importeddb not versus the current database.
There is also a undocumented system procedure, sp_MSForEachDB, that will cause a SQL statement to run versus each database. For example, to grant database access for a login named to each user database, run:
exec sp_MSForEachDB
@replacechar = N'?'
, @command1 =
'if db_id(''?'') > 4 exec [?].dbo.sp_adduser '''''
The @replacechar will be replaced by the database name.
SQL = Scarcely Qualifies as a Language
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply