1

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?

kjv
  • 11,047
  • 34
  • 101
  • 140

2 Answers2

2

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.

Jeremy Smyth
  • 23,270
  • 2
  • 52
  • 65
  • If the user already has database access, it will return an error. How can I verify if the user has database access? – kjv May 18 '10 at 14:56
  • 2
    You could run a query like `if not exists(select * from sys.database_principals where name='bobuser') begin create user...` etc to see if it exists first – Jeremy Smyth May 18 '10 at 15:00
0

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.

Thomas
  • 63,911
  • 12
  • 95
  • 141