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.

SQL SERVER - Simple Script to Create a Login and User for a Specific Database with System Admin Rights loginuser-800x220

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)

, , ,
Previous Post
SQL SERVER – How to Find Free Log Space in SQL Server?
Next Post
SQL SERVER – Reading Transaction Log to Identified Who Dropped a Table

Related Posts

4 Comments. Leave new

  • Lucas Majewski
    May 16, 2018 8:47 pm

    Uhmm, “You can just run the script as it is and it will work 100%” ? Did you try running it in SSMS connected to Azure SQL database?

    I get these errors:

    Keyword or statement option ‘default_database’ is not supported in this version of SQL Server.

    Keyword or statement option ‘role’ is not supported in this version of SQL Server.

    USE statement is not supported to switch between databases. Use a new connection to connect to a different database.

    Reply
  • does it allow xp cmd shell access if they are not an admin for the entire server or does it still require a proxy account?

    Reply
  • Hi pinal, I need to create a user for 100 databases with read-only in single instance by using script. Please share if it is available

    Reply
  • Tried this on a new laptop with SQL 2017 and I can’t login with NewLogin.

    Login failed for user ‘NewLogin’. (.Net SqlClient Data Provider)

    Reply

Leave a Reply

Menu