January 20, 2011 at 2:31 am
I am writing a script which creates the database logins and users. The script is a parameterized which takes parameters like database_name, user_name, login_name etc from the calling script.
I want to assign the user a default database (the one that was passed as a parameter). If I assign the Login to the default database, the user is not able to log in to that database. If I do 'Use database_name' and 'GO', I'll have to re-assign the variables in the script.
Is there a way to assign a user to a default database without re-assigning the variables again after the GO statement.
Regards.
January 20, 2011 at 2:45 am
I'm afraid I don't understand what the problem is. Please will you post your code to help me?
Thanks
John
January 20, 2011 at 3:05 am
/************************
** Create command to create Logins (Server Principal) and Users (Database Principal)
**********************************/
DECLARE @defaultDatabase VARCHAR(50)
SET @defaultDatabase = 'enginedb2'
DECLARE @engineDboLogin VARCHAR(50)
SET @engineDboLogin = 'Dengine_vj'
DECLARE @engineDboUser VARCHAR(50)
SET @engineDboUser = 'Dengine_vj'
DECLARE @sql VARCHAR(5000)
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = @engineDboLogin)
BEGIN
SET @sql = 'DROP LOGIN [' + @engineDboLogin + ']'
EXEC(@sql)
END
SET @sql = 'CREATE LOGIN [' + @engineDboLogin + '] WITH PASSWORD=''dev111'', DEFAULT_DATABASE=[' + @defaultDatabase + '], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'
EXEC(@sql)
EXEC sp_defaultdb @loginame=@engineDboLogin, @defdb=@defaultDatabase
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = @engineDboUser)
BEGIN
SET @sql = 'DROP USER [' + @engineDboUser + ']'
EXEC(@sql)
END
USE [enginedb2]
GO
/***** After this, I cannot use the variables. I'll have to re-assign the variables ******/
--SET @sql = 'CREATE USER [' + @engineDboUser + '] FOR LOGIN [' + @engineDboLogin + '] WITH DEFAULT_SCHEMA = dbo'
--EXEC(@sql)
CREATE USER [Dengine_vj] FOR LOGIN [Dengine_vj] WITH DEFAULT_SCHEMA = dbo
EXEC sp_addrolemember N'db_owner', N'Dengine_vj'
GO
After the USE [enginedb2] GO statement, I cannot use the variables again. I wanted to know if we can assign a database to an user w/o using the USE-GO statement.
Regards.
January 20, 2011 at 3:17 am
Variables only live as long as the batch in which they are created. GO is a batch separator, and so it has the effect of killing all variables. Remove the GO statement and your code should work.
John
January 20, 2011 at 3:21 am
I need the USE statement so that the user is created in the mentioned database. If I remove GO, will the USE statement work?
Regards.
January 20, 2011 at 3:23 am
There's only one way to find out! You may need to put an extra GO in before the CREATE USER statement, since I think the latter may have to be the first statement in a batch.
John
January 20, 2011 at 3:27 am
Even if I put a new GO before the CREATE USER, I can't use the same variables, right? I'll have to again create the variables, set their values again which defeats the purpose of making the script using variables. I'd rather hardcode it if I need to set values again.
I hope you understand the problem. I wanted to know if there is a better way to assign the newly created user to a default database.
Regards.
January 20, 2011 at 3:30 am
As I said before, try it.
You don't use the variables in or after your final CREATE TABLE statement, so it doesn't matter if you kill them.
John
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply