Granting a SQL Server Login Access to a Database - SQL Server

Question

I want to grant access to a database for a SQL Server login. I know about sp_grantdbaccess, however it is deprecated. What can I use instead and how can I check if the login does not already have access to the database?

Scenario: UserA creates database - in sys.database_principals I have an entry with name dbo and with the sid of UserA. I try to grant UserA permisions again: I try to do a select on sys.database_principals by name='UserA', however since name is dbo not UserA, I get an error - 'The login already has an account under a different user name.'. How can I determine if the user has database access without the sid?

Answer 1

You probably want CREATE USER

e.g.

CREATE USER bobuser FOR LOGIN boblogin

The sys.database_principals security catalog has a list of the database users, so you can ensure you don't already have one with that name.

Answer 2

First, you need to create a User in that database for the Login in question. To do that, use Create User. If you want to determine if the user already exists, you can use sys.database_principals catalog view by filtering for type = 'U'. If you want to determine if a given user is already in a role, you can use sys.database_role_members.