How do I associate a sql server login with the SQLAgentUserRole?

Currently a 2005 login has been granted sysadmin because I can not figure out how to grant the same login the SqlAgentUserRole. I thought I would use SSMS but there must be a trick. The role only exists in the msdb database, which the login has not been granted explicit access to. And the login does not appear in the list of Role Members or its sub windows. This should be real easy and intuitive . . .

Thanks!

Michael

You cannot associate a login with a database role. What you can do is map the login to a database user (CREATE USER/sp_adduser) and then make that user a role member (sp_addrolemember).

For understanding the distinction that exists between logins and users, have a look at: http://blogs.msdn.com/lcris/archive/2007/03/23/basic-sql-server-security-concepts-logins-users-and-principals.aspx.

Thanks

Laurentiu

|||

I was rather loose with my definitions. But thinking about it caused me to create a User in msdb for the login in question and was then able to associate the User in msdb as a Member of the fixed database role SQLAgentUserRole. I still don't know yet if this will allow me to demote the login from sysadmin and still allow the User to set-up and run his own SqlServerAgent jobs. But that is another day!

Thanks

sql