xp_logininfo for a low privillage user.
in [Programming SQL]
|
Prev: set identity_insert not works for insert statement on SQL2008
Next: reference chain - recursive CTE ?
From: Martin on 20 Jul 2010 05:21 Hi, I would appreciate some advice on the following senario. I would like to give a user access to "xp_logininfo" - the user is a low privilage user. Now, as I understand it, in order to run this xp, the user must be a sysadmin or be placed in the public role of the master database with execute permissions (from BOL). neither of which I would like to do. so I came up with the following scenario. I write a wrapper sp around xp_logininfo and inside the xp I use EXECUTE AS LOGIN = 'SA'. now this works, however I need to give the user impersonate rights on SA (as far as I am aware) - which again I do not want to do. my question is. how can I give a low privillage user the write to run xp_logininfo, without elevating their privilages. my next step is to look at signing a wrapper sp with a certificate. I would appreciate any advice on how to solve this problem. thanks in advance. cheers martin.
From: Erland Sommarskog on 20 Jul 2010 18:01 Martin (martin_remove_and_no_spam(a)martin_remove_.co.nz) writes: > I would appreciate some advice on the following senario. > > I would like to give a user access to "xp_logininfo" - the user is a low > privilage user. > > Now, as I understand it, in order to run this xp, the user must be a > sysadmin or be placed in the public role of the master database with > execute permissions (from BOL). neither of which I would like to do. The correct solution is put the call to xp_loginfino in a stored procedure that you sign with a certificate. Then you create a login from that certitifacte, and grant that login the necessary permissions. Note that this login is only a link between the cert and the perms; it is not possible to login as that login. For more details, see this article on my web site: http://www.sommarskog.se/grantperm.html -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Martin on 22 Jul 2010 03:26 Hi Erland, Thanks for pointing me to your excellent article, it is very informative. I have a question - if you don't mind. I can get a list of all certificates in a database by querying sys.certificates. I can get all logins that are tied to a certificate by querying sys.server_principles and joining to sys.certificates. I can get all users that are tied to a certificate by querying sys.database_principles and joining on sys.certificates. however, is it possible to get a list of all objects (I am thinking stored procedures) that have been signed with a particular certificate. I assume that when you run the "Add signature" command that some entry is made to system tables somewhere. Thanks in advance for your time. cheers martin. "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9DBC3694E3DYazorman(a)127.0.0.1... > Martin (martin_remove_and_no_spam(a)martin_remove_.co.nz) writes: >> I would appreciate some advice on the following senario. >> >> I would like to give a user access to "xp_logininfo" - the user is a low >> privilage user. >> >> Now, as I understand it, in order to run this xp, the user must be a >> sysadmin or be placed in the public role of the master database with >> execute permissions (from BOL). neither of which I would like to do. > > The correct solution is put the call to xp_loginfino in a stored procedure > that you sign with a certificate. Then you create a login from that > certitifacte, and grant that login the necessary permissions. Note that > this login is only a link between the cert and the perms; it is not > possible to login as that login. > > For more details, see this article on my web site: > http://www.sommarskog.se/grantperm.html > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000: > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >
From: Erland Sommarskog on 22 Jul 2010 05:53
Martin (martin_remove_and_no_spam(a)martin_remove_.co.nz) writes: > Thanks for pointing me to your excellent article, it is very informative. > > I have a question - if you don't mind. > > I can get a list of all certificates in a database by querying > sys.certificates. > I can get all logins that are tied to a certificate by querying > sys.server_principles and joining to sys.certificates. > I can get all users that are tied to a certificate by querying > sys.database_principles and joining on sys.certificates. > > however, is it possible to get a list of all objects (I am thinking stored > procedures) that have been signed with a particular certificate. Yes. The DMV to look in is sys.crypt_properties. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |