May 1, 2010 at 4:52 am
Hello, I'm having issues creating two stored procs. I would like to create one that creates and resets the password for a defined type of user on the system. Then grant two users the ability to run the two proc with the proper permissions. I'm thinking I do this. I continue to get errors when I run the procs.
Create user:
CREATE PROCEDURE dbo.Create_User
@username varchar(40)
AS
DECLARE @AuserID varchar(40)
SET @AuserID = 'A' + @username
BEGIN
SET NOCOUNT ON
DECLARE @SQL NVARCHAR(4000);
SET @SQL = 'CREATE LOGIN ' + @AuserID + ' WITH PASSWORD = ''password'', DEFAULT_DATABASE=[DatabaseName], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON';
EXECUTE(@SQL);
SET @SQL = 'CREATE USER ' + @AuserID + 'FOR LOGIN' + @AuserID
EXECUTE(@SQL)
EXEC sys.sp_addsrvrolemember @loginame = @AuserID, @rolename = N'NewRole';
END;
I receive the following error when run:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.
I receive the same error when I attempt to run this proc as well.
CREATE PROCEDURE dbo.Set_Passwd
@username varchar(40)
AS
DECLARE @AuserID varchar(40)
SET @AuserID = 'A' + @username
BEGIN
SET NOCOUNT ON
DECLARE @SQL NVARCHAR(4000);
SET @SQL = 'ALTER LOGIN ' + @AuserID + ' WITH PASSWORD = ''N''password';
EXECUTE(@SQL);
END;
May 1, 2010 at 5:24 am
ColeTrain (5/1/2010)
Hello, I'm having issues creating two stored procs. I would like to create one that creates and resets the password for a defined type of user on the system. Then grant two users the ability to run the two proc with the proper permissions. I'm thinking I do this. I continue to get errors when I run the procs.Create user:
CREATE PROCEDURE dbo.Create_User
@username varchar(40)
AS
DECLARE @AuserID varchar(40)
SET @AuserID = 'A' + @username
BEGIN
SET NOCOUNT ON
DECLARE @SQL NVARCHAR(4000);
SET @SQL = 'CREATE LOGIN ' + @AuserID + ' WITH PASSWORD = ''password'', DEFAULT_DATABASE=[DatabaseName], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON';
EXECUTE(@SQL);
SET @SQL = 'CREATE USER ' + @AuserID + 'FOR LOGIN' + @AuserID
EXECUTE(@SQL)
EXEC sys.sp_addsrvrolemember @loginame = @AuserID, @rolename = N'NewRole';
END;
I receive the following error when run:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.
I receive the same error when I attempt to run this proc as well.
CREATE PROCEDURE dbo.Set_Passwd
@username varchar(40)
AS
DECLARE @AuserID varchar(40)
SET @AuserID = 'A' + @username
BEGIN
SET NOCOUNT ON
DECLARE @SQL NVARCHAR(4000);
SET @SQL = 'ALTER LOGIN ' + @AuserID + ' WITH PASSWORD = ''N''password';
EXECUTE(@SQL);
END;
Where do we start?
Apart from the obvious, why are you accepting a 40 char username and appending one character to it? The username will only get truncated!
To use the create user statement you will have to perform a
use database
to set the database focus for the new database user. I'm assuming password, default database and rolename are dummy values here, you are using different values in the created procedure?
The following should get you started, you will need to revisit the rest of the code and amend accordingly
CREATE PROCEDURE dbo.Create_User
@username varchar(39)
AS
DECLARE @AuserID varchar(40)
SET @AuserID = 'A' + @username
BEGIN
SET NOCOUNT ON
DECLARE @SQL NVARCHAR(2000);
SET @SQL = ''
SET @SQL = 'CREATE LOGIN ' + @AuserID + ' WITH PASSWORD = ''P@ssw0rd1'', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON';
EXECUTE(@SQL);
--you need to set the database focus for the create user statement
--by specifying a use statement first
SET @SQL = 'CREATE USER ' + @AuserID + 'FOR LOGIN' + @AuserID
EXECUTE(@SQL)
EXEC sys.sp_addsrvrolemember @loginame = @AuserID, @rolename = N'NewRole';
END;
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply