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

Permission settings for views to be created

Hi,
I'm a beginner when it comes to SQL Server 2005 and user permissions.
I'm wondering, how do I set the default rights for views yet to be
created? I've only find a way to configure the already existing views.
Br,
Tom...What I need is to have a user that can only access the views in a
database (meaning access the data in tables through views).
Tom wrote:
> Hi,
> I'm a beginner when it comes to SQL Server 2005 and user permissions.
> I'm wondering, how do I set the default rights for views yet to be
> created? I've only find a way to configure the already existing views.
> Br,
> Tom|||Tom
GRANT SELECT on SCHENA ...... GRANT SELECT on VIEW .... . (See details
in the BOL) .
Do the users have SSMS installed on their workstations?
"Tom" <tom.kostiainen@.saunalahti.fi> wrote in message
news:1163166994.843815.142270@.h48g2000cwc.googlegroups.com...
> Hi,
> I'm a beginner when it comes to SQL Server 2005 and user permissions.
> I'm wondering, how do I set the default rights for views yet to be
> created? I've only find a way to configure the already existing views.
> Br,
> Tom
>|||Using a schema I can deny or grant permissions to use the alter
command, but not specifically grant alter for any view (existing or to
be created) AND deny alter for any table. I can only either grant or
deny alter, right?
Do I really have to specifically set permissions for every table and
view and not just specify a default setting for all views and table in
a database?
Uri Dimant wrote:[vbcol=seagreen]
> Tom
> GRANT SELECT on SCHENA ...... GRANT SELECT on VIEW .... . (See details
> in the BOL) .
> Do the users have SSMS installed on their workstations?
>
> "Tom" <tom.kostiainen@.saunalahti.fi> wrote in message
> news:1163166994.843815.142270@.h48g2000cwc.googlegroups.com...|||Tom
> Do I really have to specifically set permissions for every table and
> view and not just specify a default setting for all views and table in
> a database?
You can GRANT SELECT ... on database level , take a look at GRANT topic in
the BOL
"Tom" <tom.kostiainen@.saunalahti.fi> wrote in message
news:1163403167.187392.286270@.m7g2000cwm.googlegroups.com...
> Using a schema I can deny or grant permissions to use the alter
> command, but not specifically grant alter for any view (existing or to
> be created) AND deny alter for any table. I can only either grant or
> deny alter, right?
> Do I really have to specifically set permissions for every table and
> view and not just specify a default setting for all views and table in
> a database?
>
> Uri Dimant wrote:
>

Permission for a role...

Hi
I have a server login Jans_Test.
This maps to a databaseuser Jans_Test in a database
In this database I have a schema AT. Default schema for Jans_Test is AT.
I also have a Database role db_dealer created in SQL with
CREATE ROLE db_dealer
GRANT EXECUTE TO db_dealer
User Jans_Test is member of role db_dealer. The role db_dealer is owned by
Jans_Test.
The role db_dealer owns schema AT.
All objects in the database is in the schema AT
I want to be able to add new users to the role db_dealer and then they
should only have permission to connect to the database and execute stored
procedures.
So the user should only have the permissions that is granted by the role.
And the role should only have permission to connect and execute stored
procedures.
How do I accomplish this?
Best regards
Jan Nielsen> User Jans_Test is member of role db_dealer. The role db_dealer is owned by
> Jans_Test.
> The role db_dealer owns schema AT.
Permission checking is bypassed for the object owner. Consequently, you
should not specify a role (db_dealer) as the schema owner if you want to
prevent members of that role from using objects in the schema.

> I want to be able to add new users to the role db_dealer and then they
> should only have permission to connect to the database and execute stored
> procedures.
> So the user should only have the permissions that is granted by the role.
> And the role should only have permission to connect and execute stored
> procedures.
The script below specifies a database user as the AT schema owner and grants
EXECUTE permission on the AT schema to db_dealer members:
EXEC sp_addrole 'db_dealer'
GO
CREATE USER db_dealer_owner WITHOUT LOGIN;
GO
CREATE SCHEMA AT AUTHORIZATION db_dealer_owner;
GO
CREATE USER Jans_Test FOR LOGIN Jans_Test
WITH DEFAULT_SCHEMA = AT;
GO
EXEC sp_addrolemember 'db_dealer', 'Jans_Test';
GO
--test security
CREATE PROC AT.usp_TestProc AS
SELECT OBJECT_NAME(@.@.PROCID)
GO
GRANT EXEC ON SCHEMA::AT TO db_dealer;
GO
EXECUTE AS USER = 'Jans_Test';
GO
EXEC AT.usp_TestProc;
GO
REVERT;
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Jan Nielsen" <jan_a_bilinfo.dk> wrote in message
news:uQXmJbA%23GHA.4468@.TK2MSFTNGP05.phx.gbl...
> Hi
> I have a server login Jans_Test.
> This maps to a databaseuser Jans_Test in a database
> In this database I have a schema AT. Default schema for Jans_Test is AT.
>
>
> I also have a Database role db_dealer created in SQL with
> CREATE ROLE db_dealer
> GRANT EXECUTE TO db_dealer
>
> User Jans_Test is member of role db_dealer. The role db_dealer is owned by
> Jans_Test.
> The role db_dealer owns schema AT.
>
> All objects in the database is in the schema AT
>
>
> I want to be able to add new users to the role db_dealer and then they
> should only have permission to connect to the database and execute stored
> procedures.
> So the user should only have the permissions that is granted by the role.
> And the role should only have permission to connect and execute stored
> procedures.
>
> How do I accomplish this?
>
> Best regards
>
> Jan Nielsen
>|||Hi Dan
Thanks a lot for answering. I'll look into this tomorrow.
Best regards
Jan
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:718663FD-ED22-461D-97CF-81CC8A5283F6@.microsoft.com...
> Permission checking is bypassed for the object owner. Consequently, you
> should not specify a role (db_dealer) as the schema owner if you want to
> prevent members of that role from using objects in the schema.
>
> The script below specifies a database user as the AT schema owner and
> grants EXECUTE permission on the AT schema to db_dealer members:
>
> EXEC sp_addrole 'db_dealer'
> GO
> CREATE USER db_dealer_owner WITHOUT LOGIN;
> GO
> CREATE SCHEMA AT AUTHORIZATION db_dealer_owner;
> GO
> CREATE USER Jans_Test FOR LOGIN Jans_Test
> WITH DEFAULT_SCHEMA = AT;
> GO
> EXEC sp_addrolemember 'db_dealer', 'Jans_Test';
> GO
>
> --test security
> CREATE PROC AT.usp_TestProc AS
> SELECT OBJECT_NAME(@.@.PROCID)
> GO
> GRANT EXEC ON SCHEMA::AT TO db_dealer;
> GO
> EXECUTE AS USER = 'Jans_Test';
> GO
> EXEC AT.usp_TestProc;
> GO
> REVERT;
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jan Nielsen" <jan_a_bilinfo.dk> wrote in message
> news:uQXmJbA%23GHA.4468@.TK2MSFTNGP05.phx.gbl...
>

Permission confusion

It seems by default the public role is granted very generous permissions.
When I tried DENY ALL TO public is didn't seem to have any affect. I know it
will work by listing the objects or going into enterprise manager and
denying.
I am tempted to pull all permissions from public and then only allowing
absolutely needed permissions (although I don't know what those are yet).
Is there a reason for the public role to have so much power?
Is it better to make a new role and just add the guest and other accounts to
that role to limit their access instead of removing permissions from the
public role?DAC,
The Public database role doesn't have any "generous permissions". That
being said - are you refering to Pubs or Northwind? Additional permissions
are granted in these databases to make it easier for developers to learn
T-SQL. Also, if you're refering to a user-defined database that has
"generous permissions" you might want to take a look that the permissions
granted in the Model system database as all new databases are based off of
the Model system database.
HTH
Jerry
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:%23MAJ5ARxFHA.3300@.TK2MSFTNGP09.phx.gbl...
> It seems by default the public role is granted very generous permissions.
> When I tried DENY ALL TO public is didn't seem to have any affect. I know
> it will work by listing the objects or going into enterprise manager and
> denying.
> I am tempted to pull all permissions from public and then only allowing
> absolutely needed permissions (although I don't know what those are yet).
> Is there a reason for the public role to have so much power?
> Is it better to make a new role and just add the guest and other accounts
> to that role to limit their access instead of removing permissions from
> the public role?
>|||Thank you. The model database currently allows things like sysusers,
sysfiles, syscolumns, sysindexes and some procedures that I have not learned
about yet. Is this common or is it prudent to remove these permissions? Some
of what they reveal seems like giving away a lot of information, user names,
tables, columns, etc.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OG00dERxFHA.2880@.TK2MSFTNGP12.phx.gbl...
> DAC,
> The Public database role doesn't have any "generous permissions". That
> being said - are you refering to Pubs or Northwind? Additional
> permissions are granted in these databases to make it easier for
> developers to learn T-SQL. Also, if you're refering to a user-defined
> database that has "generous permissions" you might want to take a look
> that the permissions granted in the Model system database as all new
> databases are based off of the Model system database.
> HTH
> Jerry
> "DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
> news:%23MAJ5ARxFHA.3300@.TK2MSFTNGP09.phx.gbl...
>|||DAC,
I looked at my Model system database and there are permissions granted to
the public database role for may system tables/view but no stored procedures
so I'm not sure what you're seeing there. Normal? Yeah...and often time
required to allow certain sp_ functionality in SQL Server.
HTH
Jerry
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:u2HHJmRxFHA.2728@.TK2MSFTNGP14.phx.gbl...
> Thank you. The model database currently allows things like sysusers,
> sysfiles, syscolumns, sysindexes and some procedures that I have not
> learned about yet. Is this common or is it prudent to remove these
> permissions? Some of what they reveal seems like giving away a lot of
> information, user names, tables, columns, etc.
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:OG00dERxFHA.2880@.TK2MSFTNGP12.phx.gbl...
>|||Ok, thank you. It does help.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%235EulvRxFHA.1456@.TK2MSFTNGP11.phx.gbl...
> DAC,
> I looked at my Model system database and there are permissions granted to
> the public database role for may system tables/view but no stored
> procedures so I'm not sure what you're seeing there. Normal? Yeah...and
> often time required to allow certain sp_ functionality in SQL Server.
> HTH
> Jerry
> "DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
> news:u2HHJmRxFHA.2728@.TK2MSFTNGP14.phx.gbl...
>