Recently I have created a TABLE that is under Database > System Databases > master > Tables > my_table.
(refer to the image below)

Now I would like to create a user that can access this Table, but don’t know how it works. (I wanted to use C# to connect to this Table)

Recommendations

User objects shouldn’t be stored in the master database as explained in the following articles.

---

Tables in the Master Database (BrentOzar.com)

The master database is a system database – it’s not really for you to poke around in and store stuff. We usually design different backup & recovery processes for the master database as opposed to user databases.

The master database is run in Simple Recovery model and hence cannot be restored to a certain point-in-time if the database becomes corrupt.

---

Tables in the SQL Server “master” database, will they cause problems? (ServerFault.com)

I would recommend doing the move to a dedicated database. Here are a couple of reasons why:

• If you ever want to migrate this database to a different instance of SQL Server you’ll need to do this anyway. You can’t migrate master.
• The master database is not meant for user data. It’s meant to be the central point for data and code needed to keep the instance functional. Adding things in master muddies the waters there.
• Adding things to master requires changing security in master unnecessarily.

---

master Database (Microsoft TechNet)

When you work with the master database, consider the following recommendations:

• Always have a current backup of the master database available.
• Back up the master database as soon as possible after the following operations:
  • Creating, modifying, or dropping any database
  • Changing server or database configuration values
  • Modifying or adding logon accounts
• Do not create user objects in master. Otherwise, master must be backed up more frequently.
  Do not set the TRUSTWORTHY option to ON for the master database.

---

Instead

Consider creating a user database (e.g My_App) for your application.

CREATE DATABASE [My_App]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'My_App', FILENAME = N'E:DATAMy_App.mdf' , SIZE = 131072KB , FILEGROWTH = 16384KB )
 LOG ON 
( NAME = N'My_App_log', FILENAME = N'F:LOGSMy_App_log.ldf' , SIZE = 65536KB , FILEGROWTH = 16384KB )
GO

Then create an administrative user (e.g. My_App_DBO) for your database:

USE [master]
GO
CREATE LOGIN [My_App_DBO] WITH PASSWORD=N'P@ssw0rd', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
USE [My_App]
GO
CREATE USER [My_App_DBO] FOR LOGIN [My_App_DBO]
GO
USE [My_App]
GO
ALTER USER [My_App_DBO] WITH DEFAULT_SCHEMA=[dbo]
GO
USE [My_App]
GO
ALTER ROLE [db_owner] ADD MEMBER [My_App_DBO]
GO

This user will have the db_owner role on your new database and can perform DDL and DML statements against your new database.

Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database.

Now you can create your table in your new My_App database with the SQL Server Login My_App_DBO.

When you have finished configuring your database consider adding a new user that doesn’t have the db_owner role, to reduce the liability of unwanted DDL statements.

Create a new user (e.g. My_App_USER) with only db_datareader and db_datawriter permissions.

USE [master]
GO
CREATE LOGIN [My_App_USER] WITH PASSWORD=N'P@ssw0rd', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
USE [My_App]
GO
CREATE USER [My_App_USER] FOR LOGIN [My_App_USER]
GO
USE [My_App]
GO
ALTER USER [My_App_USER] WITH DEFAULT_SCHEMA=[dbo]
GO
USE [My_App]
GO
ALTER ROLE [db_datareader] ADD MEMBER [My_App_USER]
GO
USE [My_App]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [My_App_USER]
GO

A SQL Server login with the db_datawriter role can:

Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.

A SQL Server login with the db_datareader role can:

Members of the db_datareader fixed database role can run a SELECT statement against any table or view in the database.

And then go from there.

• Instead of using SQL Server logins you can assign Windows Domain users as SQL Logins.
• You could also vary the access levels on each user object you create for each individual login.

This is just a small starting point and should give you a general idea on how to keep things out of the master database.

And always ensure you are on your database when running scripts to create objects:

USE My_App

Good luck on your journey.

You create a user in master database using the same create user statement that you use in other databases; to give the permissions you use the same grant statement, but it’s not a good idea to create user tables in master and give the permissions in master too

If what you are asking is user rights assignment then you have to:

• create a login with public server role;
• create a user in master database associated with the login;
• create role in master database and assign permissions to read/write to the table to this role;
• associate the database user with that role.