SQL Server: Giving user role membership on specific DB

Just a really quick note on how to manage RBA in SQL Server not relying on SQL Server Management GUI, as sometimes it is far quicker to execute a few lines, for example to create DB and then grant dbowner role membership, for let’s say you application service account so that your app can use newly created DB. So SQL code is the following:

--Create database


--Grant dbowner role to specified user

USE YourDatabaseName

EXEC sp_addrolemember N'db_owner', N'YourDomain\YourUser'

NOTE: In real world/production environments you should not be so generous with granting dbowner role to each and every user, event to app service account. See some expanations for example here: 5 Reasons Against Allowing db_owner Role Permissions.

Leave a Reply

Your email address will not be published. Required fields are marked *