Solved

SQL Query syntax : IF NOT EXISTS?

Posted on 2009-05-13
6
1,920 Views
Last Modified: 2012-05-06
First I creat a Login to SQL Server using

CREATE LOGIN NT AUTHORITY\NETWORK SERVICE FROM WINDOWS WITH DEFAULT_DATABASE="MyDB";

Here, NT AUTHORITY\NETWORK SERVICE is the name of the Login I created and MyDB is the DataBase I associate it with.

After creating it I need to change some settings of the created login. Following is the manual procedure I usually do.

1. Open SQL Server Management Studio
2. In the "Object Explorer", expand "Security" -> "Logins"
3. Right Click the newly created login "NT AUTHORITY\NETWORK SERVICE" -> Select "Properties"
4. In the "Select a page" section select "User Mapping"
5. In the right hand side, there is a section called "Users mapped to this login". There, under the "Map" section check the CheckBox relavent to MyDB, then under "Default Schema" click the button that appears.
6. In the appearing "Select Schema" windown, click "Browse", select MyDB from the new window that opens and click OK. Then press OK in the "Select Schema" window and return to the previous window. (Bear in mind that at the time of the creation of MyDB, I also create Schemas, so that I am able to select that from "Select Schema" window. In your PC, you may not be able to do so if you don't create a schema when you create a DB.)
7. There, in the part at the bottom which says "Database role membership for : MyDB", check "db_owner". ("public" is already checked, so leave it as it is)
8. Then click OK in the Login Properties and apply the above settings.

I need this process to be done using SQL Queries, so following is the query I use.

But not just that, I also want to check if the login and the users exist before they are created.

I manged to check for the login using the following:


IF EXISTS (select loginname from master.dbo.syslogins where name = N'NT AUTHORITY\NETWORK SERVICE' and dbname = 'MyDB')

That worked fine, but I don't know how I should look for the users if they exist or not.
Any ideas?
Help with code segments will be greatly appreciated.
USE [MyDB]

GO

ALTER LOGIN [MyLogin] WITH DEFAULT_DATABASE=[MyDB]

GO

USE [MyDB]

GO

CREATE USER [MyLogin] FOR LOGIN [MyLogin]

GO

USE [MyDB]

GO

ALTER USER [MyLogin] WITH DEFAULT_SCHEMA=[mySchema]

GO

USE [MyDB]

GO

EXEC sp_addrolemember N'db_owner', N'MyLogin'

GO

Open in new window

0
Comment
Question by:sachintha81
  • 3
  • 2
6 Comments
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24372076
you can check whether user exist or not from following query

user DataBaseName
SELECT name FROM sysusers
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24372083


Declare @userName varchar(50)
set @UserName='Ritesh123'


if exists(SELECT name FROM sysusers where name=@Username)
print 'exists'
else
print 'not exists'

0
 
LVL 1

Expert Comment

by:Kerjemanov
ID: 24372351
if exists(select * from sys.syslogins WHERE name = 'sa') ...
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 31

Accepted Solution

by:
RiteshShah earned 50 total points
ID: 24372370
kerjemanov,

you gave the right solution but for checking login not use. I gave reference of SysUsers after reading

" but I don't know how I should look for the users if they exist or not" from Author's post.

if author wants to check for users than he should go for

Declare @userName varchar(50)
set @UserName='Ritesh123'


if exists(SELECT name FROM sysusers where name=@Username)
print 'exists'
else
print 'not exists'


and if he wants to check for logins than he should go for


Declare @LoginName varchar(50)
set @LoginName='Ritesh123'


if exists(SELECT name FROM sys.syslogins where name=@Username)
print 'exists'
else
print 'not exists'

0
 
LVL 1

Expert Comment

by:Kerjemanov
ID: 24372971
Author talked about logins, so I thought may be he just got confused login with user
0
 
LVL 3

Author Comment

by:sachintha81
ID: 24380943
Actually I wanted to check for both logins and users, but had already figured out a way to check for logins as I've shown in my first post. Though it is a bit different from what Ritesh has shown.

Anyway, thank you for clearing both of them out. Excellent answer!
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
ADO.NET ENTITY DATA MODEL 3 30
Syntax error 9 45
create insert script based on records in a table 4 14
How can my static class become undefined?? 8 0
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now