Creating Proxies in SQL Server By Adam Machanic, Microsoft MVP and founder of Data Education
SQL Server 2005 allows creation of both server-level principals (logins) that cannot log in, and database-level principals (users) that are not associated with a login. It is only possible to switch into the execution context of one of these users or logins via impersonation, making them ideal for privilege escalation scenarios. In order to create a proxy login (which can be used to delegate server-level permissions such as BULK INSERT or ALTER DATABASE), you must first create a certificate in the master database. Think of a certificate as a trusted way to verify the identity of a principal without a password. The following syntax can be used to create a certificate in master (note that before a certificate can be created in any database, a master key must be created):
USE master GO CREATE CERTIFICATE Dinesh_Certificate ENCRYPTION BY PASSWORD = 'stR0n_G paSSWoRdS, pLE@sE!' WITH SUBJECT = 'Certificate for Dinesh' GO
Once the certificate has been created, a proxy login can be created using SQL Server 2005 ′s CREATE LOGIN FROM CERTIFICATE syntax:
CREATE LOGIN Dinesh FROM CERTIFICATE Dinesh Certificate
This login can be granted permissions, just like any other login. However, to use the permissions, it must be mapped to a database user. This is done by creating a user using the same certificate that was used to create the login, using the CREATE USER FOR CERTIFICATE syntax. Another type of proxy principal that can be created is a database user not associated with a server login. This is done using CREATE USER WITHOUT LOGIN:
Copyright ©2012 Data Education. All Rights Reserved.
Check out our SQL Training Courses at http://dataeducation.com