Permissions system_function_schema

Hi all,
I have user defined functions in master databases such as
system_function_schema.fn_chariswhitespace
As default this kind of functions have a public permissions for EXEC, but I
want to remove this
When I execute the sentece
REVOKE EXEC ON system_function_schema.fn_chariswhitespace TO PUBLIC
I got nex error
Server: Msg 208, Level 16, State 11, Line 1
Invalid object name 'system_function_schema.fn_chariswhitespace'.
I have SQL Server 2000 64 bits.
Can anybody help me?
Thanks in advance,
Javier RosasHi,
You are using an undocumented/unsupported featured. The
system_function_schema doesn't exist in SQL 2005 and you will have
difficulties upgrading your code.
I recommend you abandon use of the system_function_schema
Regards,
Clifford Dibble
"Javier Rosas" wrote:

> Hi all,
> I have user defined functions in master databases such as
> system_function_schema.fn_chariswhitespace
> As default this kind of functions have a public permissions for EXEC, but
I
> want to remove this
> When I execute the sentece
> REVOKE EXEC ON system_function_schema.fn_chariswhitespace TO PUBLIC
> I got nex error
> Server: Msg 208, Level 16, State 11, Line 1
> Invalid object name 'system_function_schema.fn_chariswhitespace'.
> I have SQL Server 2000 64 bits.
> Can anybody help me?
> Thanks in advance,
> Javier Rosas

Permissions problem running xp_sendmail

SQL Server 2000 SP3 on Win2k Server SP3
Using code: EXEC master..xp_sendmail @.recipients=my_address_goes_here, @.mess
age='',
@.query='SELECT foo FROM bar', @.subject='Subject',
@.attachments='qry.csv',
@.attach_results='TRUE',
@.width=500,@.separator=','
Without @.attachments and @.attach_results the query runs and sends a mail con
taining data. However, it is a user requirement that I send the file as an a
ttached .csv so it will load directly into Excel. With @.attach_results set t
o TRUE and no @.attachments,
the code fails with xp_sendmail: failed with operating system error 267. I'v
e logged in as the SQL Server service account and set its TEMP and TMP envir
onment variables to a folder over which everyone has full control. When I us
e @.attachments, whether I
specify a path or not I get xp_sendmail: failed with operating system error
5.
This looks like a permissions problem, and I've seen several similar posting
s but without a solution. Can anyone point me in the right direction?
Thanks
Scott DoughtyShould also mention the server is clustered

Permissions problem running xp_sendmail

SQL Server 2000 SP3 on Win2k Server SP
Using code: EXEC master..xp_sendmail @.recipients=my_address_goes_here, @.message=''
@.query='SELECT foo FROM bar', @.subject='Subject'
@.attachments='qry.csv'
@.attach_results='TRUE'
@.width=500,@.separator=',
Without @.attachments and @.attach_results the query runs and sends a mail containing data. However, it is a user requirement that I send the file as an attached .csv so it will load directly into Excel. With @.attach_results set to TRUE and no @.attachments, the code fails with xp_sendmail: failed with operating system error 267. I've logged in as the SQL Server service account and set its TEMP and TMP environment variables to a folder over which everyone has full control. When I use @.attachments, whether I specify a path or not I get xp_sendmail: failed with operating system error 5
This looks like a permissions problem, and I've seen several similar postings but without a solution. Can anyone point me in the right direction
Thank
Scott DoughtShould also mention the server is clustered

Permission to view system tables in master database

I am trying to avoid the "Permission denied" error message when a proc tries
to select records from one of the system tables in a SQL Server 2005 master
database.
Sepcifically I need to be able to capture the IP address of the calling user
which I can get from sys.dm_exec_connections.
I have struggled with this for some time and could not find anything
directly on point in MSDN. However, I was able to cobble something together
that "appears" to work as shown below.
I am hoping that someone who has specific experience with this issue might
be able to comment on my approach and point out any issues or deficiencies.
Thanks
Dave
/*
Creating a proc that can view server state
1. Create a special login and grant it VIEW SERVER STATE permissions in
Master DB
2. Create user for special login in the User DB
3. Create proc that accesses system tables using "WITH EXECUTE AS" the
special user
4. Create a cerificate in the User DB
5. Sign the proc with the cerificate
6. Create the cerificate from backup in the Master DB
7. Create an authorization login mapped to the certificate in the Master DB
8. Grant AUTHENTICATE SERVER to the authorization login in the Master DB
9. Grant EXECUTE perms on the proc to users in the User DB
*/
--0. Create a test database
USE master
CREATE DATABASE testcert
GO
--1. Create a special login and grant it VIEW SERVER STATE permissions in
Master DB
CREATE LOGIN myViewUser WITH PASSWORD = '!Pa55word!' ;
GO
USE master
GO
GRANT VIEW ANY DEFINITION TO myViewUser ;
GO
GRANT VIEW SERVER STATE TO myViewUser ;
GO
--
--2. Create user for special login in the User DB
USe testCert
GO
CREATE USER myViewUser FOR LOGIN myViewUser ;
GO
--
--3. Create proc that accesses system tables using "WITH EXECUTE AS" the
special user
IF object_id('myPROC') IS NOT NULL
DROP PROCEDURE myPROC
GO
CREATE PROCEDURE myPROC
WITH EXECUTE AS 'myViewUser'
AS
BEGIN
DECLARE @.ip Varchar(60)
SELECT @.ip=client_net_address
FROM sys.dm_exec_connections
WHERE session_id=@.@.spid
PRINT @.ip
END
GO
--
--Test: cannot execute - The user does not have permission to perform this
action.
EXEC myPROC
--4. Create a cerificate in the User DB
CREATE CERTIFICATE myCERT
ENCRYPTION BY PASSWORD = '!Pa55word!'
WITH SUBJECT = 'Test cert',
EXPIRY_DATE = '12/05/2010';
GO
BACKUP CERTIFICATE myCERT TO FILE = 'myCERT.cer' ;
--5. Sign the proc with the cerificate
ADD SIGNATURE TO myPROC
BY CERTIFICATE myCERT
WITH PASSWORD = '!Pa55word!'
GO
--6. Create the cerificate from backup in the Master DB
USE master --Permissions at the server scope can only be granted when the
current database is master
CREATE CERTIFICATE myCert FROM FILE = 'myCERT.cer'
--7. Create an authorization login mapped to the certificate in the Master D
B
CREATE LOGIN myAuthLogin
FROM CERTIFICATE mycert ;
GO
--
--8. Grant AUTHENTICATE SERVER to the authorization login in the Master DB
-- WIEW permissions are server-level permissions, grant AUTHENTICATE SERVER
to certificate mapped login
GRANT AUTHENTICATE SERVER TO myAuthLogin
GO
--test
USE testCert
--I can execute proc
EXEC myPROC
--create some users
CREATE LOGIN testuser1 WITH PASSWORD = '!Pa55word!' ;
CREATE USER testuser1 FOR LOGIN testuser1 ;
CREATE LOGIN testuser2 WITH PASSWORD = '!Pa55word!' ;
CREATE USER testuser2 FOR LOGIN testuser2 ;
--test
execute as login = 'testuser1'
select suser_name()
--cannot execute: EXECUTE permission denied on object 'myPROC', database
'testcert', schema 'dbo'.
EXEC myPROC
revert
--
--9. Grant EXECUTE perms on the proc to users in the User DB
GRANT EXECUTE ON myPROC TO testuser1
--test again
execute as login = 'testuser1'
select suser_name()
--now can execute
EXEC myPROC
revert
execute as login = 'testuser2'
select suser_name()
--but others cabnnnot until granted perms on the proc
EXEC myPROC
revert
--Clean UP
USE testCert
GO
DROP USER testuser1
DROP LOGIN testuser1
GO
DROP USER testuser2
DROP LOGIN testuser2
GO
DROP PROC myPROC
GO
DROP USER myViewUser
DROP LOGIN myViewUser
GO
DROP CERTIFICATE myCERT
GO
USE master
GO
DROP LOGIN myAuthLogin
DROP CERTIFICATE myCERT
GO
DROP DATABASE testcert
GO
EXEC sp_configure 'show advanced options', 1 ;
GO
RECONFIGURE ;
GO
EXEC sp_configure 'xp_cmdshell', 1 ;
GO
RECONFIGURE ;
GO
--EXEC xp_cmdshell 'DIR "D:\SQLDATA\MSSQL.1\*.cer"' ;
--EXEC xp_cmdshell 'del "D:\SQLDATA\MSSQL.1\myCert.cer"' ;
EXEC xp_cmdshell 'DIR "C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\*.cer"' ;
EXEC xp_cmdshell 'del "C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\myCert.cer"' ;
EXEC xp_cmdshell 'DIR "C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\*.cer"' ;
GO
EXEC sp_configure 'xp_cmdshell', 0 ;
GO
RECONFIGURE ;After further searching I found a cleaner way to accomplish what I need to
do. See example below.
Although I am not entirely sure why this line is necessary:
-- now that we signed the procedure, we can drop the private key
alter certificate certSignCreatePrincipal remove private key;
/*
Laurentiu Cristofor's blog
SQL Server 2005: procedure signing demo
http://blogs.msdn.com/lcris/archive.../15/429631.aspx
*/
create database demo;
use demo;
CREATE LOGIN alice WITH PASSWORD = '!Pa55word!' ;
CREATE USER alice FOR LOGIN alice
-- create a procedure that selects from sys.dm_exec_connections
create procedure sp_CreatePrincipal
AS
DECLARE @.ip Varchar(60)
SELECT @.ip=client_net_address
FROM sys.dm_exec_connections
WHERE session_id=@.@.spid
PRINT @.ip
go
-- now use this newly added procedure
-- to create a low privileged principal
EXEC sp_CreatePrincipal
-- we'll now want alice to be able to use the procedure and create new
principals
-- but without granting her directly the permissions
grant execute on sp_CreatePrincipal to alice;
-- verify that alice cannot create principals
execute as login = 'alice';
EXEC sp_CreatePrincipal
revert;
-- first, we'll need to create a database master key
create master key encryption by password = 'Apufe@.))%';
-- create a certificate to sign the procedure
create certificate certSignCreatePrincipal with subject = 'for signing
procedure sp_CreatePrincipal';
-- sign procedure sp_CreatePrincipal
add signature to sp_CreatePrincipal by certificate certSignCreatePrincipal;
-- now that we signed the procedure, we can drop the private key
alter certificate certSignCreatePrincipal remove private key;
-- backup certificate to file; it will be used later to put the certificate
in master
backup certificate certSignCreatePrincipal to file =
'certSignCreatePrincipal.cer';
-- create and map a user to the certificate
--? create user u_certSignCreatePrincipal from certificate
certSignCreatePrincipal;
-- create the same certificate in master now
use master;
create certificate certSignCreatePrincipal from file =
'certSignCreatePrincipal.cer';
-- create and map a login to the certificate
create login l_certSignCreatePrincipal from certificate
certSignCreatePrincipal;
GRANT VIEW SERVER STATE TO l_certSignCreatePrincipal;
-- we're done!
use demo;
-- check that the certificate in demo matches the one in master
select c.name
from sys.certificates c
JOIN master.sys.certificates mc ON c.thumbprint = mc.thumbprint;
-- verify that alice can now create principals
execute as login = 'alice';
sp_CreatePrincipal
revert;
-- cleanup
drop user u_certSignCreatePrincipal;
drop login l_certSignCreatePrincipal;
drop procedure sp_CreatePrincipal;
drop certificate certSignCreatePrincipal;
drop user alice;
drop login alice;
drop user bob;
drop login bob;
EXEC sp_configure 'xp_cmdshell', 1 ;
RECONFIGURE ;
EXEC xp_cmdshell 'del "C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\certSignCreatePrincipal.cer"' ;
EXEC xp_cmdshell 'DIR "C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\*.cer"' ;
use master;
drop certificate certSignCreatePrincipal;
drop database demo;|||Dave (Dave@.discussions.microsoft.com) writes:
> I am trying to avoid the "Permission denied" error message when a proc
> tries to select records from one of the system tables in a SQL Server
> 2005 master database.
> Sepcifically I need to be able to capture the IP address of the calling
> user which I can get from sys.dm_exec_connections.
> I have struggled with this for some time and could not find anything
> directly on point in MSDN. However, I was able to cobble something
> together that "appears" to work as shown below.
> I am hoping that someone who has specific experience with this issue
> might be able to comment on my approach and point out any issues or
> deficiencies.
Yes, you have to do something you did, although you don't the EXECUTE
AS thing. Certificate signing is all you need. I have an article on
my web site that show how you can do this:
http://www.sommarskog.se/grantperm.html
Since you seem to have grasped some of this already, you may want to
rush through until the first BULK INSERT example.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Erland Sommarskog.
That is a very useful web site at http://www.sommarskog.se/
However, one thing I am not clear on; why do you back up the private key and
Laurentiu drops it? What is the risk of dropping it like he did?
Erland Sommarskog's example:
BACKUP CERTIFICATE reloadcert TO FILE = 'C:\temp\reloadcert.cer'
WITH PRIVATE KEY (FILE = 'C:\temp\reloadcert.pvk' ,
ENCRYPTION BY PASSWORD = 'Tomorrow never knows',
DECRYPTION BY PASSWORD = 'All you need is love')
Laurentiu Cristofor's example:
alter certificate certSignCreatePrincipal remove private key;
backup certificate certSignCreatePrincipal to file =
'certSignCreatePrincipal.cer';|||Dave (Dave@.discussions.microsoft.com) writes:
> That is a very useful web site at http://www.sommarskog.se/
> However, one thing I am not clear on; why do you back up the private key
> and Laurentiu drops it? What is the risk of dropping it like he did?
>
> Erland Sommarskog's example:
> BACKUP CERTIFICATE reloadcert TO FILE = 'C:\temp\reloadcert.cer'
> WITH PRIVATE KEY (FILE = 'C:\temp\reloadcert.pvk' ,
> ENCRYPTION BY PASSWORD = 'Tomorrow never knows',
> DECRYPTION BY PASSWORD = 'All you need is love')
>
> Laurentiu Cristofor's example:
> alter certificate certSignCreatePrincipal remove private key;
> backup certificate certSignCreatePrincipal to file =
> 'certSignCreatePrincipal.cer';
If I do it in one way, and Laurentiu another, Laurentiu is likely to be
right! (Unless it's the usage of EXECUTE AS, where we are known to
disagree. :-)
I will have to admit that I was not aware of the REMOVE PRIVATE KEY
clause. I used what I was able to get to work. Dropping the private key
seems to be a better idea. However, as I understand it, it calls for a
different order of things. To wit, I first create the certificate and the
login in master, export the cert, and then move to the target database
where I sign the procedure. As I need the privte key to sign, I do need
the private key at this point.
I should definitely spend some time reworking the example - I only
need to find that time. :-)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Permission to run master..xp_cmdshell

Hi!
I have a user (SQL login), who has access to master database and the
right to run xp_cmdshell via database role membership. When running
it:
SETUSER 'Smith'
go
exec master..xp_cmdshell 'dir c:'
go
SETUSER
=============================== We get:
Msg 50001, Level 1, State 50001
xpsql.c: Error 997 from GetPassword on line 465
When I give the user the sa server role, it works fine. I am not very
happy to give so many users the sa role. Please help me to figure out,
how to allow a user to run xp_cmdshell.
/* I suspect that this is the consequences? of a recent security patch
installation (SQL2000-KB815495-8.00.0818-ENU.exe), since there were no
complains about it before */
Thanks.I assume you are running SQL 7. This error may indicate a problem with
the proxy and/or service accounts.
To ensure the proxy account is configured properly, use Enterprise
Manager to navigate to Management --> SQL Server Agent --> Job System
and ensure 'only allow users with Sysadmin privileges ...' is unchecked
and click the Reset Proxy Account button.
If the problem persists, ensure the SQL Server service account has the
needed permissions. The easiest way to do this is to re-specify the
service account using Enterprise Manager (server properties -->
Security). Assuming you are using a domain account, change it to the
System account and then back to the domain account. Enterprise Manager
will assign the domain account the necessary rights during the change.
You'll need to restart SQL Server after the change. Sometimes a reboot
is required as well.
Separately, you might consider leveraging cross database ownership
chaining so that you don't need to grant direct execute permissions to
users on xp_cmdshell. If your proc is owned by dbo, all you need to do
is change ownership of your user database to the 'sa' login. Due to the
unbroken ownership chain, users can then execute xp_cmdshell only via
your user proc and cannot execute it directly. However, you should
employ this technique only if you fully trust users that have
permissions to create dbo-owned objects in your user database.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Roust_m" <roustam@.hotbox.ru> wrote in message
news:a388fd78.0308192241.7a70bf5f@.posting.google.com...
> Hi!
> I have a user (SQL login), who has access to master database and the
> right to run xp_cmdshell via database role membership. When running
> it:
> SETUSER 'Smith'
> go
> exec master..xp_cmdshell 'dir c:'
> go
> SETUSER
> ===============================> We get:
> Msg 50001, Level 1, State 50001
> xpsql.c: Error 997 from GetPassword on line 465
> When I give the user the sa server role, it works fine. I am not very
> happy to give so many users the sa role. Please help me to figure out,
> how to allow a user to run xp_cmdshell.
> /* I suspect that this is the consequences? of a recent security patch
> installation (SQL2000-KB815495-8.00.0818-ENU.exe), since there were no
> complains about it before */
> Thanks.

Permission Questions After running DTS TransferLogins

I moved user DBs to a new SQL 2000sp4 server running win2003r2, also moved
the MSDB, not the Master, then I used DTS to transfer user logins to the new
SQL box. Now I have problems changing some login permissions. When I try
to add a database to a login I get this error > "error 15081: membership of
the public role cannot be changed" AND there I am unable to make any changes
to the public ROLE. Can some one tell me what I am missing ' Thanks you..Hi
Using the DTS package to transfer logins will not re-create the original
SIDs therefore you would need to resolve orphanded users!
sp_change_users_login 'report' would show any orphaned users, see
http://support.microsoft.com/kb/274188/ and
http://support.microsoft.com/kb/240872 for more.
John
"WANNABE" wrote:
> I moved user DBs to a new SQL 2000sp4 server running win2003r2, also moved
> the MSDB, not the Master, then I used DTS to transfer user logins to the new
> SQL box. Now I have problems changing some login permissions. When I try
> to add a database to a login I get this error > "error 15081: membership of
> the public role cannot be changed" AND there I am unable to make any changes
> to the public ROLE. Can some one tell me what I am missing ' Thanks you..
>
>

Permission Questions After running DTS TransferLogins

I moved user DBs to a new SQL 2000sp4 server running win2003r2, also moved
the MSDB, not the Master, then I used DTS to transfer user logins to the new
SQL box. Now I have problems changing some login permissions. When I try
to add a database to a login I get this error > "error 15081: membership of
the public role cannot be changed" AND there I am unable to make any changes
to the public ROLE. Can some one tell me what I am missing ' Thanks you..Hi
Using the DTS package to transfer logins will not re-create the original
SIDs therefore you would need to resolve orphanded users!
sp_change_users_login 'report' would show any orphaned users, see
http://support.microsoft.com/kb/274188/ and
http://support.microsoft.com/kb/240872 for more.
John
"WANNABE" wrote:

> I moved user DBs to a new SQL 2000sp4 server running win2003r2, also moved
> the MSDB, not the Master, then I used DTS to transfer user logins to the n
ew
> SQL box. Now I have problems changing some login permissions. When I try
> to add a database to a login I get this error > "error 15081: membership o
f
> the public role cannot be changed" AND there I am unable to make any chang
es
> to the public ROLE. Can some one tell me what I am missing ' Thanks you
.
>
>