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
CREATE USER Bob WITHOUT LOGIN
This user, like any database user, can be assigned ownership and other permissions. However, it is impossible to log into the server and authenticate as Bob. Instead, you must log in using a valid serverlevel login and authenticate to the database with whatever database user is associated with your login. Only then can you impersonate Bob, taking on whatever permissions the user is assigned.
View this article on our website: http://dataeducation.com/blog/creating-proxies-in-sql-server
Copyright Š2012 Data Education. All Rights Reserved.
Check out our SQL Training Courses at http://dataeducation.com