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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-01-03 : 12:09:05
|
| John writes "Simple question, I want to grant access to a database other than the current one in a stored procedure based on a database name in a variable.The current syntaxsp_grantdbaccess [@loginame =] 'login'[,[@name_in_db =] 'name_in_db' [OUTPUT>Does not provide a parameter for specifying another database to add the user to and assumes the current database. Ideally it would be nice if it had a second parameter for the database name I could pass in such as the context for the command. At least this would make a lot of sense in terms of the interface. Alas, no such luck. The help file suggests:USE DatabaseNamesp_grantAccess @LoginHowever this just won't work because the USE statement will not take a variable for the database name.Next attempt:EXEC 'USE ' + @Databasename + '; sp_grantAccess UserA'This would work, except it breaks the ownership chain and causes problems when I was depending on the fact that the SA owned the stored procedure for this to execute.Help!" |
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2003-01-03 : 12:19:38
|
| use database.dbo.sp_grantdbaccess |
 |
|
|
|
|
|
Granting Access to a database other than the current one.