Adding user to Database - Security - USers

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Adding user to Database - Security - USers

Author

Topic

prodsac
Starting Member

8 Posts

Posted - 2011-04-19 : 00:25:17

hi

I use a script to create databases from a master.
DECLARE @Cnt INT,
@DBName Varchar(50),
@UserName Varchar(50),
@SQL Nvarchar(MAX)

SET @Cnt = 1

WHILE @Cnt <= 16
BEGIN
SET @DBName = 'LimsUser'+ CASE WHEN @Cnt < 10 THEN '0'+CAST(@Cnt AS VARCHAR(3))
ELSE CAST(@Cnt as Varchar(3)) END
SET @UserName = 'pcd\LimsUser'+CASE WHEN @Cnt < 10 THEN '0'+CAST(@Cnt AS VARCHAR(3))
ELSE CAST(@Cnt as Varchar(3)) END

SET @SQL = ' USE [master] '+
' CREATE DATABASE '+ QUOTENAME(@DBName) + ' ON '+
' ( FILENAME = N''C:\SQL\DBs\LIMS\Users\'+@DBName+'.mdf'' ), '+
' ( FILENAME = N''C:\SQL\DBs\LIMS\Users\'+@DBName+'_log.ldf'' ) '+
' FOR ATTACH '+

' IF EXISTS ( SELECT name '+
' FROM master.sys.databases sd '+
' WHERE name = '''+@DBName+''''+
' AND SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() ) '+
' EXEC '+@DBName+'.dbo.sp_changedbowner @loginame='''+@UserName +''''+
' ,@map=false '

EXEC sp_ExecuteSQL @SQL
SET @Cnt = @Cnt + 1
END

Can you tell me how to add:

The Database LimsUserXY as the default for the user with the same name?

And to add the LimsUserXY in the Database - Security - User of the same name?

Thanks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-19 : 07:26:07

create login has a default database, alter login if the login already exists and you want to change the default database.
create user will map the login to the database

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

prodsac
Starting Member

8 Posts

Posted - 2011-04-19 : 10:32:52

I need to create a new script?

DECLARE @Cnt INT,
@DBName Varchar(50),
@UserName Varchar(50),
@SQL Nvarchar(MAX)

SET @Cnt = 1

WHILE @Cnt <= 9
BEGIN
SET @DBName = 'LimsUser'+ CASE WHEN @Cnt < 10 THEN '0'+CAST(@Cnt AS VARCHAR(3))
ELSE CAST(@Cnt as Varchar(3)) END
SET @UserName = 'pcd\LimsUser'+CASE WHEN @Cnt < 10 THEN '0'+CAST(@Cnt AS VARCHAR(3))
ELSE CAST(@Cnt as Varchar(3)) END

SET @SQL = ' USE '+ QUOTENAME(@DBName) +
' CREATE USER '+ QUOTENAME(@UserName)

EXEC sp_ExecuteSQL @SQL
SET @Cnt = @Cnt + 1
END

I really am more a powershell guy than a t-sql lol
Can you help me?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources