Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Change the database name using stored procedure 1

Status
Not open for further replies.

VenkatSQL

Technical User
Nov 6, 2007
14
IN
Consider I am working in Database A. Using a stored procedure I want to change the database from A to master by executing that stored procedure..



I tried this one but the database is not getting changed



declare @dbname varchar(100)

declare @str varchar(100)

set @dbname = 'master'



set @str = 'use ' + @dbname

exec (@str)


it executed successfully. But the database is not changed.. Please advise
 
Why you need this?
It changes the database BUT only in the batch of EXEC, not in the current batch.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP
 
Thansk for quick response

I want to execute grant permisstion to some tables in another server database.

like

I tried the following statement but it is not working


Grant SELECT ON [servername].[databasename].[dbo].[tablename] TO [rolename]

but the same is working if we remove the servername and the database name.(Assume you are in the same server/database)

Grant SELECT ON [dbo].[tablename] TO [rolename]

thats i am trying to change the database name in the runtime

...
Please advise

Thansk for quick response
 
Hi;

If your objective is to run something in master database then you should use long name identier and call that object like that:

master.dbo.yourspname

Thanks

 
See my earlier respone.

I want to execute grant permisstion to some tables in another server database

but the same is working if we remove the servername and the database name.(Assume you are in the same server/database)
 
Do it it the same batch:
Code:
declare @dbname varchar(100)

declare @str varchar(8000)

set @dbname = 'master'

 

set @str = 'use ' + @dbname+
GRANT SELECT ON [dbo].[tablename] TO [rolename]'

exec (@str)
not tested at all and I am not sure if this will work for other server.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP
 
You are trying to alter permissions on a linked server? I don't think you can do that through a linked server connection. You have to connect to the server directly to issue GRANT commands.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top