How to specify a Windows Authentication user in T-SQL
This may only be a SQL Server 2005 problem. Comments either way please.
Only one of these works:
grant execute on stored_proc to [domain1\ismom]
grant execute on stored_proc to 'domain1\ismom'
grant execute on stored_proc to '[domain1\ismom]'
Only one of these Execute As User works:
execute as user = 'domain1\ismom'
exec master..xp_sqlagent_enum_jobs 0, 'sys-admin'
revert
execute as user = '[domain1\ismom]'
exec master..xp_sqlagent_enum_jobs 0, 'sys-admin'
revert
execute as user = [domain1\ismom]
exec master..xp_sqlagent_enum_jobs 0, 'sys-admin'
revert
The execute as user doesn't give you a syntax error, by the way. Instead it goes all cryptic:
Msg 15404, Level 16, State 11, Line 1
Could not obtain information about Windows NT group/user '[domain1\ismom]', error code 0x534.
Some consistency here would be nice. Usable error messages are a plus also.
For grant execute, use the bare brackets [domain1\ismom]
For Execute As User, use the quotes without brackets: 'domain1\ismom'