I'm writing a program that requires me to assign all permissions and everything in code. I have gotten stuck in this part:

enter image description here

I just want to do the equivalent of clicking the little box under "Map" for the msdb database and assigning that user to SqlAgentUser role. I need a user to be able to add/edit SQL Server Agent jobs. I can get the settings right using SSMS but I can't for the life of me figure out how to do it in raw SQL.

I've looked into ALTER LOGIN but I'm not seeing anything that does what I need. I suspect I just don't know the correct terms to Google. I don't normally do this sort of thing.

Any help is much appreciated!

| improve this question | | | | |
  • 3
    You can "generate script" that I think will help you in future projects. On the top of the screen. – Racer SQL Mar 26 '15 at 17:19
  • 1
    @RafaelPiccinelli oh my gosh that is so helpful! I never even noticed that up there! THank yoU! – eddie_cat Mar 26 '15 at 17:22
  • I'm really bad at codes, this help me a LOT. – Racer SQL Mar 26 '15 at 17:27
USE msdb;
ALTER ROLE SqlAgentUserRole ADD MEMBER shims;

Also, for future reference, any time you know how to do something in the UI but not in a script, this is what the Script option on most dialogs is for - it will show you what script SSMS would have executed:

enter image description here

| improve this answer | | | | |

If you want to change the current\default database to a different one try:

alter login <loginname> with default_database = <dbname>;

Now, create a user for above login created

 use <dbname>;
 create user <username> from login <loginname>;

And now you can assign roles to the above create user for the login as below:

use <dbname>
exec sp_addrolemember 'db_owner', '<username>';
| improve this answer | | | | |
  • 3
    From the docs: This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER ROLE instead. – Aaron Bertrand Mar 26 '15 at 17:18
  • So should I change the default database every time I want to do anything with this user in msdb? In the window above it seems like you can have more than one database associated with a user at a time... is that wrong? – eddie_cat Mar 26 '15 at 17:21
  • @eddie_cat No, I see no reason in this case to change the default database, unless you want that user to always connect to certain database by default. – Aaron Bertrand Mar 26 '15 at 17:23
  • To create user for the login; the correct syntax is as follows: ------------------------------------------------------------------------- USE [DatabaseName] GO CREATE USER [UserName] FOR LOGIN [LoginName] GO – Kundan Dasange Aug 2 '17 at 5:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.