64

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,143
  • 3
  • 11
  • 15

3 Answers3

104

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,146
  • 1
  • 7
  • 9
  • 3
    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
79
EXEC sp_addrolemember 'db_owner', 'JohnJacobs'

Just to let people in the future know, MS official documentation now specifies that this approach is being deprecated. ALTER ROLE is recommended intead.

Raab
  • 34,778
  • 4
  • 50
  • 65
  • 13
    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
1

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,890
  • 2
  • 25
  • 39