RE: USE Command With Dynamic Database Names Fails to Change Database

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Jesse Beveridge (jesse_at_nospam.nospam)
Date: 02/08/05 

Date: Tue, 8 Feb 2005 11:59:03 -0800

When you call EXEC('somesql') everything in the parenthesis is execute in
it's own scope.
 
>From BOL:
"Changes in database context last only until the end of the EXECUTE statement"
 
So after you create your DB's you can preface the subsequent statements with
the database name. (e.g. dbname..tablename).

"Dale Jackson" wrote:

> I am trying to create a new database using a variable name for the database
> name and then switch to the new database to create a set of tables, views and
> stored procedures.
>
> DECLARE @NewDB varchar(35),@SQLStr varchar(1000)
> SELECT @NewDB = 'MyDatabase'
>
> IF NOT EXISTS (SELECT Name FROM Master.dbo.SysDatabases WHERE
> Has_DBAccess(Name) = 1 and Name = @NewDB)
> BEGIN
> SELECT @SQLStr = 'CREATE Database '+@NewDB
> EXEC (@SQLStr)
> END
> go
>
> IF EXISTS (SELECT Name FROM Master.dbo.SysDatabases WHERE Has_DBAccess(Name)
> = 1 and Name = @NewDB)
> BEGIN
> EXEC ('USE '+@NewDB)
> SELECT DB_NAME()
> END
> go
>
> The first command is successful and creates the new database, but the second
> execute statement fails and shows that I'm still connected to the "Master"
> database. Using dynamic SQL to create the hundreds of tables, views and
> procedures from the Master database is not acceptable. Any suggestions?
>
>

Relevant Pages

  • Re: New user with no permissions can see and execute system stored procedures...
    ... Best regards ... But I can still see system views and stored procedures (though not system ... and map it to a database user and set its default schema to dbo. ... I have only tried to execute sys.sp_catalogs, but in my opinion a new ...
    (microsoft.public.sqlserver.security)
  • Re: how to secure a sql 2005 database?
    ... that's why i rather keep my stored procedures in my own source code, then pass it thru to sqlexec to execute. ... its like quickbooks turning over their internal structures, which they really don't, but provide an api to export data out. ... so are you saying that i can't create a user and make that user the only valid user to get into the database, and unless you know the password, even admins, you cant open the database at all. ...
    (microsoft.public.sqlserver.setup)
  • Re: Grant Execute right to the group of users
    ... Below is a script that will grant execute permissions to the specified role ... on all user procedures in the current database. ... > How could I grant the execute right to all stored procedures in a database for a group of users? ...
    (microsoft.public.sqlserver.programming)
  • Re: Cross-database execution permissions with certificates and sch
    ... activated user cannot access objects in other schemas in this database. ... injection is defended against, however, the threat exists anywhere EXECUTE ... CREATE USER dispatcher ... the certificate is a trusted authenticator because you granted AUTHENTICATE ...
    (microsoft.public.sqlserver.security)
  • Re: New user with no permissions can see and execute system stored procedures...
    ... map it to a database user and set its default schema to dbo. ... SQL Server Management Studio GUI) and execute _system_ stored procedures. ...
    (microsoft.public.sqlserver.security)