Re: xp_regread in SQL 2005

mjames@xxxxxxxxxx (mjames@xxxxxxxxxx) writes:
1) If I just connect to SQL server (say, with Management Studio) using
Windows Authentication (or, even, SQL Authentication) and run my
stored procedure, I can read the registry settings that I need to.
It's only when I activate the application role that the call fails.
Therefore, SQL Server is capable of reading what I need it to, it just
won't in this case.

I guess that the XP looks at the current user, and see what privileges
it has. And since an application role is a database-only principal, it
cannot have any server-level privileges. Which you obviously need to run
xp_regread.

But the fact that xp_regread permits persons with sysadmin access to
read any reg key means that you could could write a stored procedure that
you sign with a certificate that is present both in the database,
and in master. Then you associate a "login" with that certificate,
and grant that login the necessary privs. It may suffice with VIEW
SERVER STATE, but maybe you need CONTROL SERVER. I put "login" in
quotes, because that login cannot actually connect. Obviously that
procedure should not take a registry key as a parameter, but you should
embed most of the reg key into the procedure.

For more details and examples on this certificate business, see
this article on my web site: http://www.sommarskog.se/grantperm.html.

2) What is a CLR stored procedure? How do I write one? What are the
drawbacks of using it?

SQL 2005 permits you to write stored procedures (and functions, triggers
etc) in .Net languages such as C#, VB .Net etc. You can write and
deploy such procedures through Visual Studio, or compile them from
the command line, and the load the assembly into SQL Server with
CREATE ASSEMBLY. When you create the procedure you put EXTERNAL NAME
where you normally have the body.

The big advantage of CLR procedures over extended stored procedures
is that they run in a much more sheltered manner, and SAFE assemblies
cannot bring down the server like an extended stored procedure can.
They are also a lot more capable than XPs. In this case, your assembly needs
extra permissions, EXTERNAL_ACCESS or UNSAFE, which also asks for some
requirements on server level.

It's difficult to see any drawbacks. If you use the CLR, you use a
supported mechanism, instead of the unsupported xp_regread. Of course,
if you never worked with .Net before, there is a learning curve.

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: Report to display data from sql serv.
    ... IIF (case statements in SQL Server) scenarios etc. ... then you need to create a Stored Procedure in SQL Server and use that as ... at run time supplying the parameters in code by using the Exec command. ...
    (microsoft.public.access.reports)
  • Re: ADO stored proc
    ... to know how to access a SQL Server stored procedure using vbscript ... I've looked at vbscript books and sql server books on ... I don't like this technique since: ...
    (microsoft.public.scripting.vbscript)
  • Re: Stored Procedures vs DTS vs Jobs
    ... A stored procedure is complied code meaning that SQL Server has already ... > execution time, ...
    (microsoft.public.sqlserver.dts)
  • Re: Tracing user & permissions (triggers and stored p)
    ... To solve my problem I chosed to configure the SQL ... > from xp_cmdshell executes under the security context of the SQL Server ... >> sql Trigger executes a Stored Procedure passing it some variables. ...
    (microsoft.public.sqlserver.security)
  • Re: Views vs Stored Procedures, whats the difference?
    ... I hope you are not suggesting you embed SQL queries into the application? ... A stored procedure logic will be exactly as fast as the algorithm you ... I understant that SQL Server supports hints. ... implementations (nestedloop, merge, hash, ..) on decent sized tables, then ...
    (comp.databases.ms-sqlserver)