17

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!

3
  • 3
    You can "generate script" that I think will help you in future projects. On the top of the screen.
    – Racer SQL
    Mar 26, 2015 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, 2015 at 17:22
  • I'm really bad at codes, this help me a LOT.
    – Racer SQL
    Mar 26, 2015 at 17:27

2 Answers 2

25
USE msdb;
GO
CREATE USER shims FROM LOGIN shims;
GO
ALTER ROLE SqlAgentUserRole ADD MEMBER shims;
GO

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

6

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>';
4
  • 4
    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. Mar 26, 2015 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, 2015 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. Mar 26, 2015 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
    – user118167
    Aug 2, 2017 at 5:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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