SQL Server Giving Logins(Users) Db_Owner Access to Database

SQL Server giving logins(users) db_owner access to database

You need to do two things, both running in the context of the target database (i.e., execute USE (database) first):

1. Add that user as a login to the database: CREATE USER [LoginName] FOR LOGIN [LoginName]
2. Add that user to the role: EXEC sp_addrolemember N'db_owner', N'LoginName'

In general, if you have SQL Server Management Studio 2005 or higher, you can go into the UI for an operation, fill out the dialog box (in this case, assigning the user to the database & adding roles), and then click the "Script" button at the top. Instead of executing the command, it will write a script for the action to a new query window.

Programmatically set a DB user to be db_owner

To give the user DBO permissions:

EXEC sp_addrolemember N'db_owner', N'[Driver-SOC-ChrisTest]'

To make the user owner of the database (not advised):

EXEC sp_changedbowner N'[Driver-SOC-ChrisTest]'

How to grant db_owner permissions to an application role?

"You cannot place an application role into a database role" appears to be the part that's not correct. An (application) role can be added as a member of another role:

EXEC sp_addrolemember 'db_owner', 'ApplicationRoleName';

(From 2012 onwards, this procedure has been deprecated in favor of the new ALTER ROLE .. ADD MEMBER syntax.)

To GRANT CONTROL on an entire database to a role, the following will do:

USE [DatabaseName];
GRANT CONTROL ON DATABASE::[DatabaseName] TO [ApplicationRoleName];

The USE is necessary to bring the role in scope; the DATABASE:: scope qualifier is always necessary when referencing databases.

Having said all that, an application role is probably not a good candidate to grant the broadest of permissions to. The password for it is passed in plaintext unless care is taken to encrypt the connection itself, monitoring and auditing may overlook it, and it's easy to forget to revert when the permissions are no longer needed. That last part is extra insidious because an unreverted app role activation will persist across pooled connections, leaving a connection "stuck" in admin mode. Alternatives include opening a separate connection with new credentials for arbitrary actions and using stored procedures with EXECUTE AS for permissions that can't be GRANTed effectively.

When you create a new login in SQL Server it selects db_owner by default

I ran into the same issue.

The solution: sp_changedbowner. That fixed it. (Somehow the owner was corrupted)

Programmatically give access to databases

The database context reverts back to the outer context (sample_database) after the dynamic SQL USE statement runs so the subsequent sp_addrolemember runs in sample_database instead of aa1833 as intended.

Execute USE and sp_addrolemember in the same dynamic SQL batch to avoid the issue:

DECLARE @dbRights varchar(max) = 'USE ' + QUOTENAME(@DBname) + N';EXEC sp_addrolemember ''db_owner'', @Lname';
sp_executesql @dbRights, N'@Lname sysname', @Lname = @Lname;

Can I connect to the database as the user dbo?

The first part to understand is the difference between a User and a Login in SQL Server. This article provides a brief rundown to understand the difference, but to put it simply, a Login is a server-level principal that provides authentication and authorisation for server-level access. A User is a database-level principal that provides authorisation for database-level access.

There are many scenarios, but typically a Login is mapped to one or more Users, but only one User per database. A User is only ever mapped to one Login (except for contained database users, but that is a different scenario that can be ignored for now).

The dbo User is a built-in database user in every single database that represents the Database Owner. This user has full unrestricted access to the database. While you cannot login to a server as "dbo", you can login with a login that is mapped to the dbo user in one or more databases. When the database context of the connection is a database where that login is mapped to dbo, then the identity in that context is as "dbo".

In addition, as per comments, any login that has the sysadmin role will appear as dbo in every database as well, even if they're not explicitly set as the database owner.

To answer your specific questions:

During security audit we are being asked if the dbo login is
interactive. My understanding is that the account is automatically
created and managed by SQL server itself and is not directly
interactive.

Yes, the dbo account is built-in and cannot be deleted, locked, disabled or removed. It also cannot be logged into directly, i.e. you cannot login as user "dbo" with a password.

I have tried to the demonstrate that a sysadmin users would appear in
SESSION_USER as dbo when authenticating but they're not fully
convinced. Their question is now if it is possible to directly
authenticate to dbo by logging in with its password.

Is this possible? I don't even know where I would find dbo's password if it has one.

No, it is not possible. There is no "password" that exists for dbo. There may be a password for the mapped Login if it is a SQL Authentication login, but no direct login for dbo.

This article provides an explanation of dbo and also some useful queries for identifying your logins that are mapped to dbo in each DB. I would suggest you advise the auditors that it is not possible in SQL Server to login directly as dbo, nor can you adjust any permissions or disable/remove this account, then provide them the list of mappings and a list of sysadmins or logins with CONTROL SERVER permissions to illustrate who can assume the dbo user context in each database.

Granting Full SQL Server Permissions for a Database

If you literally want them to be able to do anything in that database, you can just add them to the db_owner role:

USE ContainedDatabase;
GO
ALTER ROLE db_owner ADD MEMBER [username];

If you want to be more granular, you can add them to lesser roles, like db_ddladmin, db_securityadmin, etc. You can see the list of built-in roles here:

• Database-Level Roles

The permissions inherent in each of those roles:

• Permissions of Fixed Database Roles

And if those don't suit, you can create your own roles, add your user to that role, and grant specific permissions to that role you created (and/or add them to other roles). The difference between applying the permissions to the role instead of directly to the user is simply reuse - if you add five more users that you want to apply the same permissions, you just add them to the custom role, rather than apply those granular permissions or roles to all 5 of the users.

---

Related Topics