December 17, 2014 at 5:44 am
Hello and thanks in advance -
I have been supporting a proof of concept for new vendor software at my place of employment. The vendor states that occassionally a login will need sysadm privs (once or twice a year for a few hours) to do the following functions:
• Install extended procedures on the master database
• Create new databases
• Create login information
• Create DBlinks
• Install CLR functions
This doesn't make complete sense to me. For instance, dbcreator should be fine to create a database. My company does not allow sysadm privs for any reason after installation to anyone other than DBAs, so I'm trying to find out if sysadm is really necessary or not for the app login. Is there a good reference out there to look up this kind of detailed info? Of course the alternative is to bring in different software.
December 17, 2014 at 6:04 am
random_name (12/17/2014)
• Install extended procedures on the master database
This required sysadmin. The rest do not.
That said, any vendor asking to install extended stored procs on any server I'm in any way responsible for would have to give one hell of a good explanation as to why.
A few quotes from MSDN on extended stored procs:
This feature will be removed in a future version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible.
The system administrator should thoroughly review an extended stored procedure to ensure that it does not contain harmful or malicious code before adding it to the server and granting execute permissions to other users.
The execution of an extended stored procedure has these characteristics:
The extended stored procedure function is executed under the security context of Microsoft SQL Server.
The extended stored procedure function runs in the process space of SQL Server.
The thread associated with the execution of the extended stored procedure is the same one used for the client connection.
Extended stored procedures offer performance enhancements and extend SQL Server functionality. However, because the extended stored procedure DLL and SQL Server share the same address space, a problem procedure can adversely affect SQL Server functioning. Although exceptions thrown by the extended stored procedure DLL are handled by SQL Server, it is possible to damage SQL Server data areas. As a security precaution, only SQL Server system administrators can add extended stored procedures to SQL Server. These procedures should be thoroughly tested before they are installed.
If I had no choice but to install a vendor app that used extended procs, the only way I would allow it is on an instance with no other databases, on it's own VM, with that SQL Server instance running as a low-privileged domain account which is not used for anything else and is watched for odd behaviour.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 17, 2014 at 6:24 am
on my servers, for example, the only extended stored procs are those that were isntalled when i deployed Redgate SQL Backup.
Like Gail said, other than a vendor that i'm familiar with, they would need quite a bit of justification to need an extended proc, especially if they are already using CLR; i use a lot of CLR myself, but to install those, you just need to be db_owner(and clr has to be enabled)
Lowell
December 17, 2014 at 10:30 am
Thanks so much for the quick replies, much appreciated.
I questioned the vendor on the need for extended stored procedures, and they respond that they sent me outdated information but insist on needing sysadm for the rest of the functionality listed.
Ah, fun times.
December 18, 2014 at 1:51 am
None of the rest require sysadmin, and to be honest you probably shouldn't be letting an app make random login changes. It has no business doing so (and that would violate many security and compliance policies)
To create a database requires db_creator
I don't know what DB Links are
Install CLR functions can be done with db_owner providing CLR is enabled.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 18, 2014 at 3:04 am
DB Links are generally Oracle Terminology. which can be done by using the TNSnames.ora file and defining a db link with a service name.
The equivalent in SQL Server is linked server.
I would question the vendor on what the "DB Link" aspect of their request relates to.
and you don't require sysadmin privilege for creating linked servers either as long as you do it via TSQL.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply