sp_addrolemember question

• cross

  SSCommitted

  Points: 1735

  More actions

  March 1, 2004 at 11:56 am

  #83871

  I'm reading a book that says "If you use the sp_addrolemember stored procedure to add a user to a database role, you do not need to create the user account first. SQL will create it for you."

  I tried this out with the statement:

  use mydb

  go

  exec sp_addrolemember @rolename = 'db_accessadmin', @membername = 'myuser'

  I got the message "User or role 'myuser' does not exist in this database" instead of having the user account created in the database.

  Is the book wrong or am I doing something wrong (probably the latter)

• mdaniel

  SSCarpal Tunnel

  Points: 4616

  More actions

  March 1, 2004 at 12:44 pm

  #496510

  When adding a SQL User to a role, the user must have acess to de database. If the user is a domain user, sp_addrolemember grants database access to the user, even if it doesn't have access to the server.

  Look in BOL for 'Adding a Member to a SQL Server Database Role'