Useful SQL Queries

Hai all,

Here I am going to refer few useful SQl Queries which may be useful for our programming and they al are small and yet useful, So am Mentioning few together.

Get physical Path of Database

Some times we may need to get the physical location of the database, So that we can get the path or we can manipulate the folder in which the database files stored, So we can use the following

SELECT physical_name FROM sys.database_files where physical_name like '%master.mdf' ;

Replace only the database MDF file to get the output.No need to change anything else in the Query.

Create a login in database

by using this methods we can create and assign login accounts for the databases

CREATE LOGIN login_Name WITH PASSWORD = ‘password123’, CHECK_EXPIRATION=OFF, CHECK_POLICY=ON

GO

replace the login_Name with your desired login and password also.

Create a User for database

This code will help you to create users for the individual databases and also to add the Owner role to the user for that database

USE database_name
GO

CREATE USER User_Name FOR LOGIN database_name
GO

USE database_name
GO

EXEC sp_addrolemember db_owner, User_Name
GO

Delete a User from database

This code will help you to delete users from the database

USE database_name
GO

DROP USER  User_Name
GO

 

Thank You

SHARE

Gibin Francis

Hi. I’m Software Engineer in India. I’m Web Developer, Creative Coder, Web Designer, logo Designer, Business Enthusiast, StartUp Enthusiast, Speaker, Writer and Designer. Inspired to make things looks better and Do the this in the best way.

• 
• 
• 
• 
•