I need to check if a specific login already exists on the SQL Server, and if it doesn't, then I need to add it.
I have found the following code to actually add the login to the database, but I want to wrap this in an IF statement (somehow) to check if the login exists first.
CREATE LOGIN [myUsername] WITH PASSWORD=N'myPassword',
DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
GO
I understand that I need to interrogate a system database, but not sure where to start!
This question is related to
sql-server
login
Try this (replace 'user' with the actual login name):
IF NOT EXISTS(
SELECT name
FROM [master].[sys].[syslogins]
WHERE NAME = 'user')
BEGIN
--create login here
END
Here's a way to do this in SQL Server 2005 and later without using the deprecated syslogins view:
IF NOT EXISTS
(SELECT name
FROM master.sys.server_principals
WHERE name = 'LoginName')
BEGIN
CREATE LOGIN [LoginName] WITH PASSWORD = N'password'
END
The server_principals view is used instead of sql_logins because the latter doesn't list Windows logins.
If you need to check for the existence of a user in a particular database before creating them, then you can do this:
USE your_db_name
IF NOT EXISTS
(SELECT name
FROM sys.database_principals
WHERE name = 'Bob')
BEGIN
CREATE USER [Bob] FOR LOGIN [Bob]
END
You can use the built-in function:
SUSER_ID ( [ 'myUsername' ] )
via
IF [value] IS NULL [statement]
like:
IF SUSER_ID (N'myUsername') IS NULL
CREATE LOGIN [myUsername] WITH PASSWORD=N'myPassword',
DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
GO
https://technet.microsoft.com/en-us/library/ms176042(v=sql.110).aspx
In order to hande naming conflict between logins, roles, users etc. you should check the type
column according to Microsoft sys.database_principals documentation
In order to handle special chacters in usernames etc, use N'<name>'
and [<name>]
accordingly.
USE MASTER
IF NOT EXISTS (SELECT 1 FROM master.sys.server_principals WHERE
[name] = N'<loginname>' and [type] IN ('C','E', 'G', 'K', 'S', 'U'))
CREATE LOGIN [<loginname>] <further parameters>
USE [<databasename>]
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE
[name] = N'<username>' and [type] IN ('C','E', 'G', 'K', 'S', 'U'))
CREATE USER [<username>] FOR LOGIN [<loginname>]
USE [<databasename>]
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE
[name] = N'<rolename>' and Type = 'R')
CREATE ROLE [<rolename>]
USE [<databasename>]
EXEC sp_addrolemember N'<rolename>', N'<username>'
USE [<databasename>]
GRANT SELECT ON [<tablename>] TO [<rolename>]
GRANT UPDATE ON [<tablename>] ([<columnname>]) TO [<rolename>]
GRANT EXECUTE ON [<procedurename>] TO [<rolename>]
The SQL is tested on SQL Server 2005, 2008, 2008 R2, 2014, 2016, 2017, 2019
This is for Azure SQL:
IF (EXISTS(SELECT TOP 1 1 FROM sys.sql_logins WHERE [name] = '<login>'))
DROP LOGIN [<login>];
Source: How to check whether database user already exists in Azure SQL Database
This works on SQL Server 2000.
use master
select count(*) From sysxlogins WHERE NAME = 'myUsername'
on SQL 2005, change the 2nd line to
select count(*) From syslogins WHERE NAME = 'myUsername'
I'm not sure about SQL 2008, but I'm guessing that it will be the same as SQL 2005 and if not, this should give you an idea of where t start looking.
As a minor addition to this thread, in general you want to avoid using the views that begin with sys.sys* as Microsoft is only including them for backwards compatibility. For your code, you should probably use sys.server_principals. This is assuming you are using SQL 2005 or greater.
First you have to check login existence using syslogins view:
IF NOT EXISTS
(SELECT name
FROM master.sys.server_principals
WHERE name = 'YourLoginName')
BEGIN
CREATE LOGIN [YourLoginName] WITH PASSWORD = N'password'
END
Then you have to check your database existence:
USE your_dbname
IF NOT EXISTS
(SELECT name
FROM sys.database_principals
WHERE name = 'your_dbname')
BEGIN
CREATE USER [your_dbname] FOR LOGIN [YourLoginName]
END
what are you exactly want check for login or user ? a login is created on server level and a user is created at database level so a login is unique in server
also a user is created against a login, a user without login is an orphaned user and is not useful as u cant carry out sql server login without a login
maybe u need this
check for login
select 'X' from master.dbo.syslogins where loginname=<username>
the above query return 'X' if login exists else return null
then create a login
CREATE LOGIN <username> with PASSWORD=<password>
this creates a login in sql server .but it accepts only strong passwords
create a user in each database you want to for login as
CREATE USER <username> for login <username>
assign execute rights to user
GRANT EXECUTE TO <username>
YOU MUST HAVE SYSADMIN permissions or say 'sa' for short
you can write a sql procedure for that on a database
create proc createuser
(
@username varchar(50),
@password varchar(50)
)
as
begin
if not exists(select 'X' from master.dbo.syslogins where loginname=@username)
begin
if not exists(select 'X' from sysusers where name=@username)
begin
exec('CREATE LOGIN '+@username+' WITH PASSWORD='''+@password+'''')
exec('CREATE USER '+@username+' FOR LOGIN '+@username)
exec('GRANT EXECUTE TO '+@username)
end
end
end
Source: Stackoverflow.com