Cannot get sql to work with the quotes

Last Post 08 Jan 2008 05:21 AM by TRACEYSQL. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
TRACEYSQL
New Member
New Member

--
07 Jan 2008 09:21 AM
select 'USE [REPORT60] ' + ' GO '
+ ' CREATE USER ' + name + ' FOR LOGIN ' + name
+ 'ALTER USER ' + name + ' WITH DEFAULT_SCHEMA=' + name
+ 'CREATE SCHEMA ' + name + 'AUTHORIZATION ' + name
+ 'EXEC sp_addrolemember N'
+ 'db_datareader'
+ '''
+ ','
+ 'N'
+ '''
+ name ''''
from sys.syslogins

Im trying to do this below but get all logins from the database so that
i can give them access to the database and create a schema for it.
Stuck on the quotes part.

USE [REPORTCP60]
GO
CREATE USER [ADION] FOR LOGIN [ADION]
GO
USE [REPORTCP60]
GO
ALTER USER [ADION] WITH DEFAULT_SCHEMA=[ADION]
GO
USE [REPORTCP60]
GO
CREATE SCHEMA [ADION] AUTHORIZATION [ADION]
GO
USE [REPORTCP60]
GO
EXEC sp_addrolemember N'db_datareader', N'ADION'
GO
TRACEYSQL
New Member
New Member

--
07 Jan 2008 10:08 AM
Managed to get script generated for all users in database

declare @var1 nvarchar(20)
set @var1 = '''db_datareader'''
--print @var1

select 'USE [REPORTCP60] ' + ' GO '
+ ' CREATE USER ' + '[' + name + ']' + ' FOR LOGIN ' + '[' + name + ']' + ' GO ' +
' USE [REPORTCP60] ' + ' GO ' + char(13)
+ 'ALTER USER ' + '[' + name + ']' + ' WITH DEFAULT_SCHEMA=' + '[' + name + ']' + ' GO ' +
' USE [REPORTCP60] ' + ' GO ' + char(13)
+ 'CREATE SCHEMA ' + '[' + name + ']' + ' AUTHORIZATION ' + '[' + name + ']' + ' GO ' +
' USE [REPORTCP60] ' + ' GO ' + char(13) +
+ 'EXEC sp_addrolemember N'
+ @var1
+ ','
+ 'N'
+ ''''
+ name + ''''
from sys.syslogins
where name = 'ARahman'


This generates
USE [REPORTCP60] GO CREATE USER [ARahman] FOR LOGIN [ARahman] GO USE [REPORTCP60] GO ALTER USER [ARahman] WITH DEFAULT_SCHEMA=[ARahman] GO USE [REPORTCP60] GO CREATE SCHEMA [ARahman] AUTHORIZATION [ARahman] GO USE [REPORTCP60] GO EXEC sp_addrolemember N'db_datareader',N'ARahman'

If i just copy and paste in query analyzer it does not work it has to be layed out like this

USE [REPORTCP60]
GO
CREATE USER [ARahman] FOR LOGIN [ARahman]
GO
USE [REPORTCP60]
GO
ALTER USER [ARahman] WITH DEFAULT_SCHEMA=[ARahman]
GO
USE [REPORTCP60]
GO
CREATE SCHEMA [ARahman] AUTHORIZATION [ARahman]
GO
USE [REPORTCP60]
GO
EXEC sp_addrolemember N'db_datareader',N'ARahman'
GO

How to get it to layout i tried char(13) and char(10)

Thanks
SQLUSA
New Member
New Member

--
08 Jan 2008 04:18 AM
Flip the result window into the TEXT mode?

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/order2005admi...dhighperf/ The Best SQL Server 2005 Training in the World!
TRACEYSQL
New Member
New Member

--
08 Jan 2008 05:21 AM
Yes got it working now put it to text and used the CHAR(10)

Thanks a bunch
You are not authorized to post a reply.

Acceptable Use Policy