60

After creating roles and granting privileges to them, I want to grant the privileges of a specified role to a user, how to do with it? I tried grant to , but failed.

tmj
  • 1,103
  • 3
  • 10
  • 15

3 Answers3

90

Because BOL shows sp_addrolemember has been deprecated, if you are using SQL Server 2012 you may want to use:

ALTER ROLE <role_name> ADD MEMBER <user_name>
samp
  • 1,006
  • 7
  • 9
  • 2
    Interesting, as if this is the case then you would expect SSMS to support generation of such scripts. Which it does not. – adolf garlic Jan 04 '16 at 12:21
  • 2
    It is also worth to note that you might need to execute `CREATE USER [user_name] FOR LOGIN [user_name] WITH DEFAULT_SCHEMA=[dbo]` before that. In my case I had to do that before it worked. – Matt Sep 12 '19 at 13:33
70
EXEC sp_addrolemember 'db_owner', 'JohnJacobs'
Raab
  • 33,432
  • 4
  • 48
  • 63
  • 10
    Just to let people in the future know, MS official [documentation](https://msdn.microsoft.com/en-us/library/ms187750.aspx) now specifies that this approach is being deprecated and `sp_addrolemember` will dissapear from future releases. ALTER ROLE is the recommended way now, so follow @samp answer instead. – Alfabravo Nov 22 '16 at 21:08
0

In my case, I needed to add the user to the public role for a specific database. This is done as so:

USE [<DB Name>]
GO
CREATE USER [<User Name>] FOR LOGIN [<User Name>]
GO
Slogmeister Extraordinaire
  • 2,600
  • 2
  • 24
  • 38