Permit view and deny table?

This has probably been asked before, but is there a way to allow a user to
access a view but deny access to the underlying table? If so, how is this
done (even as a kluge)?
Thanks!For a user to be given select permissions on a view but not a table
referenced by the view, the view and underlying table must have the same
owner. This forms an ownership chain. As long as that is unbroken then the
permission check will be on the view and not the underlying table. By not
granting explict permissions on your tables and the user will not be able to
select from them.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Neil W." <neilw@.netlib.com> wrote in message
news:uflVC0shEHA.3320@.TK2MSFTNGP11.phx.gbl...
> This has probably been asked before, but is there a way to allow a user to
> access a view but deny access to the underlying table? If so, how is this
> done (even as a kluge)?
> Thanks!
>

Permissions Management

Hello,
I am trying to set up permissions to secure databases in our Developement. I
have set up "View any Database" to Deny at the Database level.
when i login as admin i see all the database while the rest see Master and
Temp DB. What do i have to do at the database level so i can make other
databases visible to appropriate personnel and also give them control such as
Create Tables, Update SP etc.
Thanks
MannyManny
If the user is an owner of db try the following
Create a new SQL login "login1"
? Create a user named ?login1? in master database
? Grant CREATE DATABASE to login1
? While impersonating login1, create a database called ?dbteste?
? Revoke CREATE DATABASE permission from login1
? Revoke VIEW ANY DATABASE permission from PUBLIC
? Register this server as login1
? From the ?login1? session, expand database tree. Now, you should see
master, tempdb, dbteste
? Grant VIEW ANY DATABASE to PUBLIC
? From the ?login1? session, you should see all the databases
"Manny Chohan" <MannyChohan@.discussions.microsoft.com> wrote in message
news:5E4EAE5B-E79D-408C-98AC-946C8EC0566E@.microsoft.com...
> Hello,
> I am trying to set up permissions to secure databases in our Developement.
> I
> have set up "View any Database" to Deny at the Database level.
> when i login as admin i see all the database while the rest see Master and
> Temp DB. What do i have to do at the database level so i can make other
> databases visible to appropriate personnel and also give them control such
> as
> Create Tables, Update SP etc.
> Thanks
> Manny|||So basically the user who is creating the database will be only one who can
view the database in the tree.
What i am trying to do is following:
I have users User1, User2, User3 and Databases as DB1, DB2 and DB3.
I would like to revoke Public from seeing all the databases.
User 1 should have access to DB1 (as DBO) and DB2 as read/write
User 2 should only have access to DB2 (DBO)
User 3 should have access to DB2 (DBO) and DB3 as read only.
Hope i am clear in explaining things
THanks
Manny
"Uri Dimant" wrote:
> Manny
> If the user is an owner of db try the following
> Create a new SQL login "login1"
> â?¢ Create a user named â'login1â' in master database
> â?¢ Grant CREATE DATABASE to login1
> â?¢ While impersonating login1, create a database called â'dbtesteâ'
> â?¢ Revoke CREATE DATABASE permission from login1
> â?¢ Revoke VIEW ANY DATABASE permission from PUBLIC
> â?¢ Register this server as login1
> â?¢ From the â'login1â' session, expand database tree. Now, you should see
> master, tempdb, dbteste
> â?¢ Grant VIEW ANY DATABASE to PUBLIC
> â?¢ From the â'login1â' session, you should see all the databases
>
>
> "Manny Chohan" <MannyChohan@.discussions.microsoft.com> wrote in message
> news:5E4EAE5B-E79D-408C-98AC-946C8EC0566E@.microsoft.com...
> > Hello,
> >
> > I am trying to set up permissions to secure databases in our Developement.
> > I
> > have set up "View any Database" to Deny at the Database level.
> >
> > when i login as admin i see all the database while the rest see Master and
> > Temp DB. What do i have to do at the database level so i can make other
> > databases visible to appropriate personnel and also give them control such
> > as
> > Create Tables, Update SP etc.
> >
> > Thanks
> >
> > Manny
>
>|||Manny,
VIEW ANY DATABASE is a server level permission and cannot be granted nor
denied at the database level. To accomplish what you wish to do, you can
deny the permission to the public role (rather than to individual users) and
put users in the db_owner role of the database(s) they should have access.
This would then allow them to see only databases which they have ownership
of.
With regards to granting/revoking DDL permissions, I suggest you try out the
tool SQL CodeSecure. It allows you to set DDL permissions very easily, as
well as audit all changes modifications made to a database and even rollback
changes or recover deleted objects.
You can download the application from this location:
http://www.sql-labs.com/downloads/SQLCodeSecure.zip
HTH.
"Manny Chohan" <MannyChohan@.discussions.microsoft.com> wrote in message
news:5E4EAE5B-E79D-408C-98AC-946C8EC0566E@.microsoft.com...
> Hello,
> I am trying to set up permissions to secure databases in our Developement.
> I
> have set up "View any Database" to Deny at the Database level.
> when i login as admin i see all the database while the rest see Master and
> Temp DB. What do i have to do at the database level so i can make other
> databases visible to appropriate personnel and also give them control such
> as
> Create Tables, Update SP etc.
> Thanks
> Manny

Permissions grants, denies don't take effect

Hi,

I'm trying to grant/deny object permissions in a user database using Enterprise Manager and query tool without success (for ‘public’ role and individual sql logins). I’m not getting any error messages. Permission changes just don’t take effect. Although, there are few objects, which already have permissions granted and I'm able to change permissions for these ones.

How can I change the permissions?

you might have a granted permission on a GUEST account

or an NT domain group. please check.

thats why denying wont work

|||there is no permissions granted or denied for the 'guest' account. I don't use domain groups.|||

What are the actual denies that you are trying to set. Did you verify that they appear in the system catalog (server_permissions, database_permissions)? Have you tried applying the denies using TSQL: DENY?

Thanks
Laurentiu

Permissions for tables and Sprocs

Hi everyone,
We are creating a new application in .net and i would like to deny
access of the tables to users, but allow them to perform routines by
stored procedures. I am using Sql2005, and was wondering if anyone had
some knowlegde about this and point me in the right direction? Has
anyone set this up before?
ThanksThis is standard practice. As long as you don't use dynamic SQL in the procedures and the procedure
and table has the same owner, then the user only need permissions to the procedures. See
http://www.sommarskog.se/grantperm.html for some elaboration.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Nemisis" <darrens2005@.hotmail.com> wrote in message
news:1166175982.773661.294100@.73g2000cwn.googlegroups.com...
> Hi everyone,
> We are creating a new application in .net and i would like to deny
> access of the tables to users, but allow them to perform routines by
> stored procedures. I am using Sql2005, and was wondering if anyone had
> some knowlegde about this and point me in the right direction? Has
> anyone set this up before?
> Thanks
>|||Tibor Karaszi wrote:
> This is standard practice. As long as you don't use dynamic SQL in the procedures and the procedure
> and table has the same owner, then the user only need permissions to the procedures. See
> http://www.sommarskog.se/grantperm.html for some elaboration.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
Tibor,
Thanks for the quick reply, and i will look at the link you sent. One
problem though, we do use dynamic Sql within some of the sprocs, using
the execute_sql sproc?
Can you explain why this makes a difference?|||> Can you explain why this makes a difference?
that is just the way it is. SQL Server doesn't know what string you are about to execute, so it
cannot allow the user of the proc to "execute just anything" without permission checking. I.e., a
conscious design decision. See the article, Erland mentions tow ways in 2005 to handle this
(signature and certificate or EXECUTE AS).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Nemisis" <darrens2005@.hotmail.com> wrote in message
news:1166180348.976164.146510@.16g2000cwy.googlegroups.com...
> Tibor Karaszi wrote:
>> This is standard practice. As long as you don't use dynamic SQL in the procedures and the
>> procedure
>> and table has the same owner, then the user only need permissions to the procedures. See
>> http://www.sommarskog.se/grantperm.html for some elaboration.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
> Tibor,
> Thanks for the quick reply, and i will look at the link you sent. One
> problem though, we do use dynamic Sql within some of the sprocs, using
> the execute_sql sproc?
> Can you explain why this makes a difference?
>|||Tibor,
I have read a couple articles now and feel that Execute As seems to be
the easiest option for the moment, and talks about setting up another
user to execute the code as. With this in mind, some articles say
about assigning this new user to a Role in the database, and th Role is
actually what is assigned the permissions. Do you agree with this?
Also, when they say role, do they mean a database role, or application
role? I am not sure i am understanding the differences between the
two, or why one should be used rather then the other.
Thanks again for all your help so far|||Database roles are containers for database users and other roles. Roles are
commonly used for security because you can grant permissions to the role
once and then control security for individual users using role membership.
Application roles are different beasts. An app role is a database security
context that can be activated in application code using sp_setapprole. Once
activated, it remains in effect until the connection is closed or
sp_unsetapprole is activated. App roles provide a means to elevate
permissions while a user is using an application and prevent direct ad-hoc
database access.
Regarding procedure permissions, like Tibor suggested, normal ownership
chaining works well except when you execute SQL dynamically or need to run
statements that require more permissions. In those cases, you can specify
an alternate security context using certificates or EXECUTE AS. You can
peruse Erland's article for details.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Nemisis" <darrens2005@.hotmail.com> wrote in message
news:1166184171.866934.154250@.80g2000cwy.googlegroups.com...
> Tibor,
> I have read a couple articles now and feel that Execute As seems to be
> the easiest option for the moment, and talks about setting up another
> user to execute the code as. With this in mind, some articles say
> about assigning this new user to a Role in the database, and th Role is
> actually what is assigned the permissions. Do you agree with this?
> Also, when they say role, do they mean a database role, or application
> role? I am not sure i am understanding the differences between the
> two, or why one should be used rather then the other.
> Thanks again for all your help so far
>

Permissions

How can i Deny access to my domain users to my sql databases and grant acces
s
only to some users. If a make Deny to all users in domain and if a create th
e
necessary users with Grant Access to some databases, the can access the
databases?
Best RegardsCreate a group and add the users you want tio deny access
Take a look at below stored procedure in the BOL.
sp_revokedbaccess [ @.name_in_db = ] 'name'
"icrp@.msn.com" <icrpmsncom@.discussions.microsoft.com> wrote in message
news:9F945ED7-1C19-44CC-A15E-C44D30CCEB65@.microsoft.com...
> How can i Deny access to my domain users to my sql databases and grant
> access
> only to some users. If a make Deny to all users in domain and if a create
> the
> necessary users with Grant Access to some databases, the can access the
> databases?
> Best Regards|||Hi,
what happen if you create seprate user and assign each of them a
permissions , it will be hectic if # users are large , rather if you create
a
ROLE(department wise) and then assign them a proper permissions and then map
a user / group ( it could be sql or windows user/group) . it will save your
time and purpose tooo.
HTH
Regards
--
Andy Davis
Activecrypt Team
---
SQL Server Encryption Software
http://www.activecrypt.com
"Uri Dimant" wrote:

> Create a group and add the users you want tio deny access
> Take a look at below stored procedure in the BOL.
> sp_revokedbaccess [ @.name_in_db = ] 'name'
>
> "icrp@.msn.com" <icrpmsncom@.discussions.microsoft.com> wrote in message
> news:9F945ED7-1C19-44CC-A15E-C44D30CCEB65@.microsoft.com...
>
>

permission problem?

hi,
for security reason, I changed the "BUILTIN\Administrators" Server Access
from "Permit" to "Deny".
but the SQLSERVERAGENT was failed to start. got this error as follow(even I
chagne the service logon account to another window("power user" group's)
user).
Any ideas?
Thanks...
Error:
Event Type: Error
Event Source: SQLSERVERAGENT
Event Category: Service Control
Event ID: 103
Date: 08/03/2004
Time: 12:51:02 PM
User: N/A
Computer: LL_SERVER
Description:
SQLServerAgent could not be started (reason: Unable to connect to server
'(local)'; SQLServerAgent cannot start).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.The windows account that Agent uses need to be able to login to SQL Server.
You removed the Administrators account from SQL Server, and couldn't login,
which indicates that the service account for Agent is Administrator (or some
other Windows account which is member of the Administrators group). So far
no surprise.
The windows group Power Users are not added as logins to SQL Server by the
installation program. Make sure that the service account for Agent has a
login in SQL Server and that login has sysadmin permissions in SQL Server.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"js" <js@.someone@.hotmail.com> wrote in message
news:ewdgfaTBEHA.3284@.TK2MSFTNGP09.phx.gbl...
> hi,
> for security reason, I changed the "BUILTIN\Administrators" Server Access
> from "Permit" to "Deny".
> but the SQLSERVERAGENT was failed to start. got this error as follow(even
I
> chagne the service logon account to another window("power user" group's)
> user).
> Any ideas?
> Thanks...
> Error:
> Event Type: Error
> Event Source: SQLSERVERAGENT
> Event Category: Service Control
> Event ID: 103
> Date: 08/03/2004
> Time: 12:51:02 PM
> User: N/A
> Computer: LL_SERVER
> Description:
> SQLServerAgent could not be started (reason: Unable to connect to server
> '(local)'; SQLServerAgent cannot start).
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
>
>|||Thanks Tibor.
I defined a user "BackupUser" as Power Users group. and add SQL sysadmin
permission to it. In order to test, I need to relogin to window as
"BackupUser".
I'm able to to login to SQL.
But when I try to start the SQL Agent service. still got an error:
Service msg box:
Could not start the SQLSERVERAGENT service on Local Computer.
Error5: Access is denied.
How to fix this one?
Another question is: If I don't login to windows(interact mode), the sql
jobs are able to run?
Thanks again.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23lAmNuTBEHA.1548@.TK2MSFTNGP12.phx.gbl...
> The windows account that Agent uses need to be able to login to SQL
Server.
> You removed the Administrators account from SQL Server, and couldn't
login,
> which indicates that the service account for Agent is Administrator (or
some
> other Windows account which is member of the Administrators group). So far
> no surprise.
> The windows group Power Users are not added as logins to SQL Server by the
> installation program. Make sure that the service account for Agent has a
> login in SQL Server and that login has sysadmin permissions in SQL Server.
>|||What error messages do you have in the SQL Server Agent errorlog? Perhaps it
is quite simply a matter of Agent doesn't have permissions on the registry
keys or directories/files that it needs.
No, you don't have to be logged in interactively in order for your Agent
jobs to run.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"js" <js@.hotmail.com> wrote in message
news:eBBqWMUBEHA.2440@.TK2MSFTNGP12.phx.gbl...
> Thanks Tibor.
> I defined a user "BackupUser" as Power Users group. and add SQL sysadmin
> permission to it. In order to test, I need to relogin to window as
> "BackupUser".
> I'm able to to login to SQL.
> But when I try to start the SQL Agent service. still got an error:
> Service msg box:
> Could not start the SQLSERVERAGENT service on Local Computer.
> Error5: Access is denied.
> How to fix this one?
> Another question is: If I don't login to windows(interact mode), the sql
> jobs are able to run?
> Thanks again.
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:%23lAmNuTBEHA.1548@.TK2MSFTNGP12.phx.gbl...
> Server.
> login,
> some
far
the
Server.
>|||Thanks Tibor.
There is no errorlog. only a messagebox pupup.
Error5: Access is denied.
The user is belong to a Power User group. Still need more permissions for
agent servie to run?
Can I do this instead:
Reable "permit" access to BUILTIN\Administraotrs, So the local system
account won't block.
and deny access for individual admin instead: domain\admin1, domain\admin2.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u8qw4XUBEHA.3748@.tk2msftngp13.phx.gbl...
> What error messages do you have in the SQL Server Agent errorlog? Perhaps
it
> is quite simply a matter of Agent doesn't have permissions on the registry
> keys or directories/files that it needs.
> No, you don't have to be logged in interactively in order for your Agent
> jobs to run.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "js" <js@.hotmail.com> wrote in message
> news:eBBqWMUBEHA.2440@.TK2MSFTNGP12.phx.gbl...
sysadmin
> in
(or
> far
> the
a
> Server.
>|||SQL Agent has an errorlog file, which you for instance can get to from EM,
Management, Right-click Agent.
I suggest you read in Books Online about permissions. Search for "level
token" and you will only get one hot, describing security needed to be in
place for the service accounts.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"js" <js@.someone@.hotmail.com> wrote in message
news:%23V1beiUBEHA.1380@.TK2MSFTNGP10.phx.gbl...
> Thanks Tibor.
> There is no errorlog. only a messagebox pupup.
> Error5: Access is denied.
> The user is belong to a Power User group. Still need more permissions for
> agent servie to run?
> Can I do this instead:
> Reable "permit" access to BUILTIN\Administraotrs, So the local system
> account won't block.
> and deny access for individual admin instead: domain\admin1,
domain\admin2.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:u8qw4XUBEHA.3748@.tk2msftngp13.phx.gbl...
Perhaps
> it
registry
> sysadmin
sql
wrote
> (or
So
by
has
> a
>|||Thanks Tibor.
I try to start the service from EM, got this:
Service Control Failure:
An error 1053 - (The service did not respond to the start or control request
in a timely
fashion) occured while performing this service operation on the
SQLServerAgent serve.
I already followed by the steps in "Setting up Windows Services Accounts"
for adding the foler, registry permission.
what I'm missing?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%236uNvkUBEHA.3928@.TK2MSFTNGP11.phx.gbl...
> SQL Agent has an errorlog file, which you for instance can get to from EM,
> Management, Right-click Agent.
> I suggest you read in Books Online about permissions. Search for "level
> token" and you will only get one hot, describing security needed to be in
> place for the service accounts.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "js" <js@.someone@.hotmail.com> wrote in message
> news:%23V1beiUBEHA.1380@.TK2MSFTNGP10.phx.gbl...
for
> domain\admin2.
> in
> Perhaps
> registry
Agent
> sql
> wrote
SQL
couldn't
Administrator
group).
> So
Server
> by
> has
>|||Still, I need to know the error from Agent, in the Agent error file (you
didn't follow the direction I gave). You can also find the file in:
C:\Program Files\Microsoft SQL Server\MSSQL$FRESH\LOG
And the file name is SQLAGENT.OUT
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"js" <js@.someone@.hotmail.com> wrote in message
news:%23Qg4WuUBEHA.3348@.TK2MSFTNGP11.phx.gbl...
> Thanks Tibor.
> I try to start the service from EM, got this:
> Service Control Failure:
> An error 1053 - (The service did not respond to the start or control
request
> in a timely
> fashion) occured while performing this service operation on the
> SQLServerAgent serve.
> I already followed by the steps in "Setting up Windows Services Accounts"
> for adding the foler, registry permission.
> what I'm missing?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:%236uNvkUBEHA.3928@.TK2MSFTNGP11.phx.gbl...
EM,
in
> for
wrote
> Agent
the
> SQL
> couldn't
> Administrator
> group).
> Server
Agent
SQL
>|||Thanks Tibor.
There is SQLAGENT.OUT file in my SQL LOG folder.
There are some ERRORLOG.x and SQLAGENT.x files( x is a number)
if I specify the wrong account password for the Agent service. then I got
the error:
An error 1069 - (The service did not start due to a logon failure) occured
while performing this service
operation on the SQLServerAgent service.
If I specify it correctly, after waiting for a while(hourglass on EM) and
then got another error:
An error 1053 - ( The service did not respond to the start or control
request in a timely fashion) occured while performing this service operation
on the SQLServerAgent servcie.
again, no SQLAGENT.OUT file under that SQL log folder.
what is next?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OMd$BzUBEHA.2600@.TK2MSFTNGP12.phx.gbl...
> Still, I need to know the error from Agent, in the Agent error file (you
> didn't follow the direction I gave). You can also find the file in:
> C:\Program Files\Microsoft SQL Server\MSSQL$FRESH\LOG
> And the file name is SQLAGENT.OUT
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "js" <js@.someone@.hotmail.com> wrote in message
> news:%23Qg4WuUBEHA.3348@.TK2MSFTNGP11.phx.gbl...
> request
Accounts"
> in
> EM,
"level
> in
permissions
system
> wrote
as
error:
> the
<tibor_please.no.email_karaszi@.hotmail.nomail.com>
to
> Agent
> SQL
>|||Sorry.
There is no SQLAGENT.OUT file in my SQL LOG folder.
"js" <js@.someone@.hotmail.com> wrote in message
news:uay0hiVBEHA.3548@.TK2MSFTNGP10.phx.gbl...
> Thanks Tibor.
> There is SQLAGENT.OUT file in my SQL LOG folder.
> There are some ERRORLOG.x and SQLAGENT.x files( x is a number)
> if I specify the wrong account password for the Agent service. then I got
> the error:
> An error 1069 - (The service did not start due to a logon failure) occured
> while performing this service
> operation on the SQLServerAgent service.
> If I specify it correctly, after waiting for a while(hourglass on EM) and
> then got another error:
> An error 1053 - ( The service did not respond to the start or control
> request in a timely fashion) occured while performing this service
operation
> on the SQLServerAgent servcie.
> again, no SQLAGENT.OUT file under that SQL log folder.
> what is next?
>
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:OMd$BzUBEHA.2600@.TK2MSFTNGP12.phx.gbl...
> Accounts"
wrote
from
> "level
be
> permissions
> system
errorlog?
the
your
SQL
window
> as
> error:
mode),
> <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> to
in
>

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...
>