HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMajor

How can I map a login to a database using T-SQL (not SSMS)

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
canmapssmssqllogindatabaseusinghownot

Problem

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!

Solution

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:

Code Snippets

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

Context

StackExchange Database Administrators Q#96358, answer score: 25

Revisions (0)

No revisions yet.