SQL SERVER – Creating a Login and Database User Without Automatic Sysadmin Grants

To Create a Login and User, I separate the instance identity from database access. Database ownership and sysadmin membership have different scopes.

-- Administrator templates, not executed during this review.
-- Substitute an existing approved Windows group and target database.
-- USE [master];
-- CREATE LOGIN [YourDomain\ApprovedGroup] FROM WINDOWS;
-- USE [YourDatabase];
-- CREATE USER [ApprovedGroup] FOR LOGIN [YourDomain\ApprovedGroup];
-- ALTER ROLE [db_datareader] ADD MEMBER [ApprovedGroup];

-- Database-wide administrator, only if explicitly required:
-- ALTER ROLE [db_owner] ADD MEMBER [ApprovedGroup];
-- Instance-wide administrator, a separate and much broader grant:
-- USE [master];
-- ALTER SERVER ROLE [sysadmin] ADD MEMBER [YourDomain\ApprovedGroup];

The first path maps a Windows login to a database user with a read-only role. Use narrower object permissions when needed. The administrative grants are separate optional examples. They are not steps everyone should run.

db_owner gives extensive control within one database. sysadmin gives control across the instance and enters databases as dbo. Adding db_owner does not restrict that login to one database. I previously described those scopes too loosely.

My old script used a weak password, created a database, granted both roles, and dropped the database afterward. I no longer recommend that recipe. Use an approved identity and a disposable teaching lab. Treat the old script as historical material.

The login-versus-user video accompanies this article. Verify intended access in a separate connection. Assign an administrative role only when the task requires it.

Related reading

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

Original video

Database ownership is not instance administration, it is authority within one database.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.