How to use Create Loing into Stored Procedure

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

General SQL Server Forums

New to SQL Server Programming

How to use Create Loing into Stored Procedure

Author

Topic

Golash69
Starting Member

2 Posts

Posted - 2010-07-17 : 07:04:04

Hi,
I Try to use Create Loing in my SP:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_AddNewUser]
-- Add the parameters for the stored procedure here
@NewUserName varchar(20),
@Password varchar(20)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
CREATE LOGIN @NewUserName WITH PASSWORD=@Password, DEFAULT_DATABASE=[ShowMeTheMoney], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
CREATE USER @NewUserName FOR LOGIN @NewUserName
EXEC sp_addrolemember N'db_datareader', @NewUserName
END

and i get the folling error:

Msg 102, Level 15, State 1, Procedure sp_AddNewUser, Line 15
Incorrect syntax near '@NewUserName'.
Msg 319, Level 15, State 1, Procedure sp_AddNewUser, Line 15
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

When is use sp_AddLogin and sp_AddUser instead Create Login and Create User it's work.

Microsoft set the sp_AddLogin and sp_AddUser as Deprecate SP.

Tnx

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-18 : 13:43:08

http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/ad63208a-48d1-4307-9275-5bf20a6386a5

No, you're never too old to Yak'n'Roll if you're too young to die.

Golash69
Starting Member

2 Posts

Posted - 2010-07-19 : 15:22:55

Tnx, now it's work great
This is the Fix SP:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_AddNewUser]
-- Add the parameters for the stored procedure here
@NewUserName varchar(20),
@Password varchar(20)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
declare @SQLCreateLogin as nvarchar(max)
declare @SQLCreateUser as nvarchar(max)
SET @SQLCreateLogin = 'CREATE LOGIN [' + @NewUserName + '] WITH PASSWORD= ''' + @Password + ''', DEFAULT_DATABASE= [' + DB_NAME() + '], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'
SET @SQLCreateUser = 'CREATE USER [' + @NewUserName +'] FOR LOGIN ['+ @NewUserName + ']'
EXEC sp_executesql @SQLCreateLogin
EXEC sp_executesql @SQLCreateUser
EXEC sp_addrolemember N'db_datareader', @NewUserName
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources