Change the database name using stored procedure 1

[VenkatSQL]

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

 

[bborissov]

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

 

[VenkatSQL]

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

 

[memdiscount]

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

 

[VenkatSQL]

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)

 

[bborissov]

Do it it the same batch:

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

 

[ESquared]

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.