SQL SERVER – Simple Script to Create a Login and User for a Specific Database with System Admin Rights

A very popular question I often receive about SQL Server security is what is the difference between SQL Server Login and SQL Server User. I really love this question as I bet only 5% of SQL Server professionals I met know the answer to this question. In this blog post, we are going to see a Simple Script to Create a Login and User for a Specific Database with System Admin Rights.

However, we continue I suggest you watch the video associated here which will help you to understand the difference between SQL Server Login and SQL Server User.

Now let us see a sample script where we will learn how we can create a login for the server and also create a user for the database. Additionally, the script will also make the user as an administrator for the database as well.

You can just run the script as it is and it will work 100%

CREATE DATABASE [NewDatabase]
GO
CREATE LOGIN [NewLogin] WITH PASSWORD=N'test', DEFAULT_DATABASE=[NewDatabase]
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [NewLogin]
GO
USE [NewDatabase]
GO
CREATE USER [NewUser] FOR LOGIN [NewLogin]
GO
ALTER USER [NewUser] WITH DEFAULT_SCHEMA=[dbo]
GO
ALTER ROLE [db_owner] ADD MEMBER [NewUser]
GO
-- Clean up
USE master
GO
DROP DATABASE [NewDatabase]
GO
DROP LOGIN [NewLogin]
GO

Let me know if you have any further question on this topic after watching the video and trying out the script which I have attached in this blog. If you have any suggestion about system admin rights or SQL Server User or Logins, please share the same in comments.

Here are few alternative blog posts:

SQL SERVER – Restore Database Wizard in SSMS is Very Slow to Open

Reference: Pinal Dave (https://blog.sqlauthority.com)