syscomments

RocketScientist's Miscellaneous Ramblings
posts - 76, comments - 354, trackbacks - 3

Tuesday, August 18, 2009

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'

posted @ Tuesday, August 18, 2009 11:26 AM | Feedback (0) |

Powered by:
Powered By Subtext Powered By ASP.NET