Hi Bill Thanks for the information and documentation.
I checked a few things out.
My username which belongs to the sysadmin role and when I run the procedure
that you included
right before the Exec stored procedure says "You are not a sysadmin.....".
So what do I need to change?
I will try and give you some settings here:
SQLServer2000 is on a Windows2000 Server
Logged into Domain as Administrator on that machine my user
has Administraors Access.
SQLServer startup account starts with a domain\Admin account and password
In SQLserver I have a SQL account which belongs to
system Admin server role
security Admin server role
server Admin server role
bulk insert Admin server role
Under the tab database access I have full access to all databases
One more thing I ran the Create Table #srvrolemember query.
Where does this reside? Where did I create this?
And how do I remove?
> Hi Gerry,
> Here is what should be the problem.
> From BOL "Transact-SQL Reference" chapter, "EXECUTE" topic, "Permissions"
> EXECUTE permissions for a stored procedure default to the owner of the
> stored procedure, who can transfer them to other users. **Permissions to
> use the statement(s) within the EXECUTE string are checked at the time
> EXECUTE is encountered, even if the EXECUTE statement is included within a
> stored procedure.** When a stored procedure is run that executes a string,
> permissions are checked in the **context of the user who executes the
> procedure**, not in the context of the user who created the procedure.
> } (my emphasis)
> From BOL "Transact-SQL Reference" chapter, "xp_cmdshell" topic, "Remarks"
> xp_cmdshell will be executed under the security context in which the SQL
> Server service is running. When the user is **not** a member of the
> **sysadmin** group, xp_cmdshell will impersonate the **SQL Server Agent
> proxy account**, which is specified using xp_sqlagent_proxy_account.
> } (my emphasis)
> And although the BOL "Administering SQL Server" chapter, "Adding a Member
> to a Predefined Role" topic, "Fixed Server Roles" section
> d_security_6ndx.asp) says:
> Windows NT 4.0 or Windows 2000 users who are members of the
> BUILTIN\Administrators group are members of the sysadmin fixed server role
> that does **not** mean that an NT administrator will always become a
> sysadmin. For example, see Q263712 "INF: How to Impede Windows NT
> Administrators from Administering a Clustered [sic - billhol] SQL Server"
> (http://support.microsoft.com/support/kb/articles/q263/7/12.asp). Thus it
> is important to verify whether the user (who was running that stored
> procedure) is a member of sysadmin fixed server role in order to determine
> the security context of the stored procedure's EXEC(...xp_cmdshell...)
> statement. Here is one approach (that should be run while logged in to SQL
> Server as that user):
> CREATE TABLE #srvrolemember
> ServerRole SYSNAME
> , MemberName SYSNAME
> , MemberSID VARBINARY(85)
> INSERT #srvrolemember
> EXEC master..sp_helpsrvrolemember 'sysadmin'
> FROM #srvrolemember
> WHERE MemberName = SUSER_SNAME()
> PRINT 'You are a sysadmin. SQL Server will use the SQL Server startup
> account''s NT permissions.'
> PRINT 'You are not a sysadmin. SQL Server will use the SQL Server Agent
> proxy account''s NT permissions.'
> Thus the permissions of the NT account (used to log into SQL Server) does
> not matter too much (apart from determining role membership). In contrast,
> what needs to be carefully considered is either the NT permissions for
> SQL Server service startup account or the NT permissions for NT's SQL
> Server Agent proxy account (and the applicable NT account depends upon the
> role membership of a login).
> As an alternative to the above deductive approach, you could determine the
> account empirically by running the attached Windows 2000 Resource Kit's
> whoami.exe via xp_cmdshell (perhaps put it in C:\ and specify that path
> whoami.exe) within xp_cmdshell's command-line string.
> Most security issues are convoluted (IMHO <g>), but there are important
> reasons for what appears to be obfuscation: We cannot allow people to
> out from SQL Server (using the security context of SQL Server) unless they
> are first identified by SQL Server to be a sysadmin. And we cannot allow a
> user to dynamically execute T-SQL (within a stored procedure or otherwise)
> unless we first ensure they have permissions to execute. Impersonation on
> behalf of a client acount, and the ability (or inability) of that client
> account to logon locally to the SQL Server box are yet other issues <g>.
> Bill Hollinshead
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no
> rights. Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.