check existence of a database principal inside a stored proc

Question

Hi Guys,

I am preparing a simple SP to create new login and add user permissions to it. 

I am facing issues here

IF NOT EXISTS(select name from @database.sys.database_principals where name=@newlogin)

How can i verify the existence of a database principal dynamically inside a SP

create procedure createnewlogin
@newlogin varchar(500),
@password varchar(500),
@database varchar(500)
as
BEGIN
DECLARE @SQL varchar(500)
DECLARE @SQL1 varchar(500)
DECLARE @SQL2 varchar(500)
select @SQL= 'use master;'+'CREATE LOGIN ['+@newlogin+'] with password='+''''+@password+''''
select @SQL1='use'+@database+';CREATE USER ['+@newlogin+'] FOR LOGIN ['+@newlogin+']'
select @SQL2='use'+@database+';EXEC SP_ADDROLEMEMBER '+''''+'db_datareader'+''''+','+ ''''+@newlogin+''''
IF NOT EXISTS(select name from sys.syslogins where name=@newlogin)
	begin
	exec sp_executesql @SQL
	END
IF NOT EXISTS(select name from @database.sys.database_principals where name=@newlogin)
	begin
	exec sp_executesql @SQL1
	exec sp_executesql @SQL2
	END
END
GO

Thanks

Answer 1

IF NOT EXISTS(select name from @database.sys.database_principals ...

You can not use a variable like @database for an object name, that's not possible, you would have to use dynamic SQL as you do to create the login.

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 2

You can't use a variable as a database name in static T-SQL statements. In this case, you could build a single dynamic T-SQL batch. Also, use ALTER ROLE instead of the deprecated sp_addrolemember proc. below is an example.

ALTER PROCEDURE createnewlogin
	@newlogin sysname,
	@password sysname,
	@database sysname
AS

DECLARE @SQL nvarchar(MAX);
SET @SQL = N'IF NOT EXISTS(SELECT 1 FROM sys.server_principals WHERE name = N''' + @newlogin + N''') CREATE LOGIN '+ QUOTENAME(@newlogin) +' WITH PASSWORD='+N''''+@password + N''';';
SET @SQL += N'USE '+@database+';IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = N''' + @newlogin + N''') CREATE USER '+QUOTENAME(@newlogin)+' FOR LOGIN '+QUOTENAME(@newlogin) + N';';
SET @SQL += N' ALTER ROLE db_datareader ADD MEMBER ' + QUOTENAME(@newlogin) + N';';
--PRINT @SQL;
EXEC sp_executesql @SQL;
GO

This proc is vulnerable to SQL injection so I'll assume no users have execution permissions and it's used only by trusted sysadmin role members as for utility purposes.

---

Dan Guzman, Data Platform MVP, http://www.dbdelta.com

Answer 3

Here is (in my opinion) a prettier version of Dan's procedure. It may also be less vulnerable to SQL injection.

CREATE OR ALTER PROCEDURE createnewlogin
	@newlogin sysname,
	@password sysname,
	@database sysname
AS

DECLARE @SQL nvarchar(MAX);
SET @SQL = N'IF NOT EXISTS(SELECT 1 FROM sys.server_principals WHERE name = @newlogin)
             CREATE LOGIN '+ QUOTENAME(@newlogin) +' WITH PASSWORD='+ quotename(@password, '''')
PRINT @SQL
EXEC master.sys.sp_executesql @SQL, N'@newlogin sysname', @newlogin
SET @SQL = 'IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = @newlogin)
                CREATE USER '+QUOTENAME(@newlogin)+' FOR LOGIN '+QUOTENAME(@newlogin) + N';
            ALTER ROLE db_datareader ADD MEMBER ' + QUOTENAME(@newlogin) + N';';
PRINT @SQL;
DECLARE @sp_executesql nvarchar(500) = @database + '.sys.sp_executesql'
EXEC @sp_executesql @SQL, N'@newlogin sysname', @newlogin

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 4

Hi udhayan,

 

I think Erland has given a great suggestion. Please following his advice.

 

By the way ,when I tested Erland's script, I found a little error. In his script, you can see " SET @SQL = N'IF NOT EXISTS(SELECT 1 FROM sys.server_principals WHERE name = @newlogin)CREATE LOGIN '+", in your original script you use 'sys.syslogins'. You maybe change the first 'sys.server_principals' into 'sys.syslogins' and  then you will get the expected result.

 

Hope it can help you .

 

Best Regards,

Rachel

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 5

Thanks for your valuable time Guys..That worked like a charm.