Granting Full SQL Server Permissions for a Database

Question

How can I give a user (who was created WITHOUT LOGIN) full control over a contained database without having to specify that database's name, like GRANT CONTROL ON DATABASE::DatabaseName TO UserName, but without using a database name? I figure it'll include GRANT ALTER ANY SCHEMA TO UserName, but I'm not sure what else I'd need to grant, or if there's a better way. Thanks.

Answer 1

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.

Answer 2

1. Open SQL Server Management Studio and connect to your server.
2. In the Object Explorer, expand the "Security" folder under the server.
3. Right click on the "Logins" folder and choose "New Login..."
4. Add the users name in the format "Domain\UserName". You can also add domain groups by just changing it to "Domain\GroupName". 5.If you would like this user to have full access to the SQL Server instance, you can choose the "Server Roles" tab. Adding the role "sysadmin" will give them full access to the server to do actions like update the database, backup the database, delete the database.

5. Click ok and your user will be created and have access to your database.

6. Choose the "User Mapping" tab. In the top half of this screen, check the box next to the database name. Once you highlight the database and check the box to map the user to it, you can add role memberships to the user. For access to the database.

7. Click ok and your user will be created and have access to your database.