I’m writing a program that requires me to assign all permissions and everything in code. I have gotten stuck in this part:
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!
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:
If you want to change the currentdefault 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>';