Prev: Automating a Cluster Group Failover
Next: syntax error (at stored proc)?
From: Justin D. on 19 Feb 2010 15:05
Hi
I am trying to use backslash as a part of a parameter at stored procedure.
And I am getting syntax error.

For instance, to execute like this..

EXEC spSetupPermissions
CORP\ABC,
Sale

with parameter I have set up like this..

CREATE PROC [dbo].[spSetupPermissions]
@USERNAME varchar(50),
@DEPARTMENT varchar(12)
AS
IF @DEPARTMENT = 'Sale'
BEGIN
EXEC Sale.dbo.sp_addlogin @loginame= @USERNAME
EXEC Sale.dbo.sp_addrolemember 'db_owner', @USERNAME
END
......etc...

I should I go about doing this?
I would appreciate if anyone would give me some lead.

Thanks.

From: Tom Cooper on 19 Feb 2010 15:14
Enclose strings you are passing in single quotes, e.g.,
EXEC spSetupPermissions
'CORP\ABC',
'Sale';

Tom
"Justin D." <JustinD(a)discussions.microsoft.com> wrote in message
news:A94D8321-C93A-4605-906E-3CA69D437666(a)microsoft.com...
> Hi
> I am trying to use backslash as a part of a parameter at stored procedure.
> And I am getting syntax error.
>
> For instance, to execute like this..
>
> EXEC spSetupPermissions
> CORP\ABC,
> Sale
>
> with parameter I have set up like this..
>
> CREATE PROC [dbo].[spSetupPermissions]
> @USERNAME varchar(50),
> @DEPARTMENT varchar(12)
> AS
> IF @DEPARTMENT = 'Sale'
> BEGIN
> EXEC Sale.dbo.sp_addlogin @loginame= @USERNAME
> EXEC Sale.dbo.sp_addrolemember 'db_owner', @USERNAME
> END
> .....etc...
>
> I should I go about doing this?
> I would appreciate if anyone would give me some lead.
>
> Thanks.
>

From: Justin D. on 19 Feb 2010 16:16
Hi Tom,

I am still getting error as
'CORP\ABC' is not a valid name because it contains invalid characters.

"Tom Cooper" wrote:

> Enclose strings you are passing in single quotes, e.g.,
> EXEC spSetupPermissions
> 'CORP\ABC',
> 'Sale';
>
> Tom
> "Justin D." <JustinD(a)discussions.microsoft.com> wrote in message
> news:A94D8321-C93A-4605-906E-3CA69D437666(a)microsoft.com...
> > Hi
> > I am trying to use backslash as a part of a parameter at stored procedure.
> > And I am getting syntax error.
> >
> > For instance, to execute like this..
> >
> > EXEC spSetupPermissions
> > CORP\ABC,
> > Sale
> >
> > with parameter I have set up like this..
> >
> > CREATE PROC [dbo].[spSetupPermissions]
> > @USERNAME varchar(50),
> > @DEPARTMENT varchar(12)
> > AS
> > IF @DEPARTMENT = 'Sale'
> > BEGIN
> > EXEC Sale.dbo.sp_addlogin @loginame= @USERNAME
> > EXEC Sale.dbo.sp_addrolemember 'db_owner', @USERNAME
> > END
> > .....etc...
> >
> > I should I go about doing this?
> > I would appreciate if anyone would give me some lead.
> >
> > Thanks.
> >
>
> .
>
From: Justin D. on 19 Feb 2010 17:51
It appears that "EXEC sp_addlogin" would not accept backslash "\".
http://msdn.microsoft.com/en-us/library/ms173768.aspx

I tried using "USE" instead, but it would not work in a stored procedure.
Error message:
"a USE database statement is not allowed in a procedure, function or trigger."

Example:
USE Sale
CREATE USER [CORP\ABC] FOR LOGIN [CORP\ABC] ;

Is there any other way I could apply in a stored procedure (applying
different databases)?

Thanks.

"Justin D." wrote:

> Hi Tom,
>
> I am still getting error as
> 'CORP\ABC' is not a valid name because it contains invalid characters.
>
> "Tom Cooper" wrote:
>
> > Enclose strings you are passing in single quotes, e.g.,
> > EXEC spSetupPermissions
> > 'CORP\ABC',
> > 'Sale';
> >
> > Tom
> > "Justin D." <JustinD(a)discussions.microsoft.com> wrote in message
> > news:A94D8321-C93A-4605-906E-3CA69D437666(a)microsoft.com...
> > > Hi
> > > I am trying to use backslash as a part of a parameter at stored procedure.
> > > And I am getting syntax error.
> > >
> > > For instance, to execute like this..
> > >
> > > EXEC spSetupPermissions
> > > CORP\ABC,
> > > Sale
> > >
> > > with parameter I have set up like this..
> > >
> > > CREATE PROC [dbo].[spSetupPermissions]
> > > @USERNAME varchar(50),
> > > @DEPARTMENT varchar(12)
> > > AS
> > > IF @DEPARTMENT = 'Sale'
> > > BEGIN
> > > EXEC Sale.dbo.sp_addlogin @loginame= @USERNAME
> > > EXEC Sale.dbo.sp_addrolemember 'db_owner', @USERNAME
> > > END
> > > .....etc...
> > >
> > > I should I go about doing this?
> > > I would appreciate if anyone would give me some lead.
> > >
> > > Thanks.
> > >
> >
> > .
> >
From: Erland Sommarskog on 19 Feb 2010 21:18
Justin D. (JustinD(a)discussions.microsoft.com) writes:
> It appears that "EXEC sp_addlogin" would not accept backslash "\".
> http://msdn.microsoft.com/en-us/library/ms173768.aspx
>
> I tried using "USE" instead, but it would not work in a stored
> procedure. Error message: "a USE database statement is not allowed in a
> procedure, function or trigger."
>
> Example:
> USE Sale
> CREATE USER [CORP\ABC] FOR LOGIN [CORP\ABC] ;
>
> Is there any other way I could apply in a stored procedure (applying
> different databases)?

In your previous post, you used sp_addlogin, now you are using CREATE USER,
those are two different things. Maybe it was sp_adduser to sp_grantdbaccess
you had in mind?

Anyway, CREATE USER is what you should use. A way to go is:

SELECT @execsql = @DEPARTMENT + '..sp_executesql'
SELECT @sql = N'CREATE USER ' + quotename(@USERNAME)
EXEC @execsql @sql


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Automating a Cluster Group Failover
Next: syntax error (at stored proc)?