Database Roles

Hi,
My understanding of db roles is that you don't have to explicitly set
permissions on objects as they role should give them this.
For example is I assign db_datareader to a user, then they automatically
have "Select" permissions on all tables
Is this right or am I going mad
SimonYes, assign a role to a user and the user will get the permissions defined
by the roles. Just like groups in any product. :-)
--
Tibor Karaszi
"Simon McDermott" <simon.mcdermott@.bicsystems.com> wrote in message
news:%23Gi8MV5oDHA.1884@.TK2MSFTNGP09.phx.gbl...
> Hi,
> My understanding of db roles is that you don't have to explicitly set
> permissions on objects as they role should give them this.
> For example is I assign db_datareader to a user, then they automatically
> have "Select" permissions on all tables
> Is this right or am I going mad
> Simon
>|||Strange
I have assigned this user to the role, but was still getting Select
permissions denied errors. What else could be wrong?
Simon
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:3S5qb.32418$mU6.93605@.newsb.telia.net...
> Yes, assign a role to a user and the user will get the permissions defined
> by the roles. Just like groups in any product. :-)
> --
> Tibor Karaszi
>
> "Simon McDermott" <simon.mcdermott@.bicsystems.com> wrote in message
> news:%23Gi8MV5oDHA.1884@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > My understanding of db roles is that you don't have to explicitly set
> > permissions on objects as they role should give them this.
> >
> > For example is I assign db_datareader to a user, then they automatically
> > have "Select" permissions on all tables
> >
> > Is this right or am I going mad
> >
> > Simon
> >
> >
>|||Perhaps you have assigned DENY permissions somehow? Deny is always stronger.
--
Tibor Karaszi
"Simon McDermott" <simon.mcdermott@.bicsystems.com> wrote in message
news:%23MJ8qj5oDHA.1948@.TK2MSFTNGP12.phx.gbl...
> Strange
> I have assigned this user to the role, but was still getting Select
> permissions denied errors. What else could be wrong?
> Simon
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:3S5qb.32418$mU6.93605@.newsb.telia.net...
> > Yes, assign a role to a user and the user will get the permissions
defined
> > by the roles. Just like groups in any product. :-)
> >
> > --
> > Tibor Karaszi
> >
> >
> > "Simon McDermott" <simon.mcdermott@.bicsystems.com> wrote in message
> > news:%23Gi8MV5oDHA.1884@.TK2MSFTNGP09.phx.gbl...
> > > Hi,
> > >
> > > My understanding of db roles is that you don't have to explicitly set
> > > permissions on objects as they role should give them this.
> > >
> > > For example is I assign db_datareader to a user, then they
automatically
> > > have "Select" permissions on all tables
> > >
> > > Is this right or am I going mad
> > >
> > > Simon
> > >
> > >
> >
> >
>

Database Role Security Permissions

Hi,

How can we determine permissions of a database role?

I could only find out how to determine user and login perms:

EXECUTE AS user = 'Omid' GO SELECT has_perms_by_name(db_name(), 'DATABASE', 'ANY') GO REVERT GOAny suggestions?I can't believe there is no reply after a day in MSDN forums. Anyway not to disappoint folks with the same problem, there is a very very stupid solution: Create a temp user(login) and add this user to the database role and then check the permissions and at last remove the user!|||

Hey, sometimes we all have to take a break

Are you using 2005? If so, you can use the sys.database_permissions view. Here is a blog that I forgot that I wrote about this until I started doing some research for you

http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!1485.entry

This query gets table permissions in a database, with object names... Easy enough to expand for other types of objects:

select database_permissions.permission_name,
coalesce(objects.type_desc,database_permissions.class_desc)
+ case when objects.type_desc is not null and minor_id > 0 then '-COLUMN'
else '' end as object_type,
case database_permissions.class_desc
when 'SCHEMA' then schema_name(major_id)
when 'OBJECT_OR_COLUMN' then
case when minor_id = 0 then object_name(major_id)
else (select object_name(object_id) + '.'+ name
from sys.columns
where object_id = database_permissions.major_id
and column_id = database_permissions.minor_id) end
else 'other' end as object_name,
database_principals.name as database_principal,
database_permissions.state_desc as grant_state
from sys.database_permissions
join sys.database_principals
on database_permissions.grantee_principal_id = database_principals.principal_id
left join sys.objects --left because it is possible that it is a schema
on objects.object_id = database_permissions.major_id
where database_permissions.major_id > 0
and permission_name in ('SELECT','INSERT','UPDATE','DELETE')
order by object_name

|||

I really appreciate it. Although I've already implemented the "stupid" solution, I'll surely change it to use the query as soon as possible.

Thanks again.

database role secureables not shows up

Hello!

After creating a new database role I have add some permissions. Now, when I want to view the role an the permissions the secureables page are empty.

This problem is only within the management studio. If I use Enterprise Manager I can see that the changes are taking effect.

Any hints?

Lutz

I see the same, I will open a bug on this.

If you would like to know whether the the permissions are granted to an object, right click the object and select ->Properties and view the permissions tab

Thanks,

Gops Dwarak

Database permissions

Hi,

In my web app I've got two databases (the asp profile one and my own custom one). If I take the code and data and use it to create another website on another box, I usually get a database error saying the permissions are wrong. In this case I usually just give everyone full control and it works, but this obviously isn't good practice!

So, I'm wondering what permissions on database files does ASP need? Which users need what permissions?

Thanks

The most reliable way to move a SQL Server database and permissions is Backup and Restore all other methods sometimes will not move permissions so you have to go in and add those as needed. There are two permissions in SQL Server needed to run Asp.net both are covered in the second thread and the first thread is a FAQ I created to help with moving SQL Server database from one computer to another. The permissions you give a user is at you discretion and that is covered under SQL Server object permissions in SQL Server BOL(books online). Hope this helps.

http://forums.asp.net/thread/1454694.aspx

http://forums.asp.net/thread/1492092.aspx

|||

Hi,

That's great thanks. The only problem is that my database is stored on a .mdf file and I cannot open it in MS SQL server management studio to change the database permissions. Is there a way around this?

Thanks

|||

That is not good practice so the SQL Server team have created a tutorial to help you connect your user instance to Management Studio when you do that you can go in and change the permissions. I would not advice you to give the everyone group full control but don't remove it from your Asp.net folders either because Asp.net is also a member of that group. Post again if you still have questions. Hope this helps.

http://blogs.msdn.com/sqlexpress/archive/2006/11/22/connecting-to-sql-express-user-instances-in-management-studio.aspx

Database Permissions

Hello All,
I have a need to set up database security on our QA and Production servers
in the following manner:
IT Managers - Read/write access. Ability to view/start/stop scheduled jobs
not owned by them (all jobs are owned by sa).
Team Leads - Allow them to create/drop/alter stored procedures and functions
only. Otherwise, read-only access to all other objects
Developers - Read-only access to all objects.
For the IT Managers, I have a couple of options. 1) Give dbo permissions,
which will give them everything but the ability to view/start/stop jobs. I
won't give them sysadmin rights.
For the Developers, it's pretty easy. db_datareader permissions,
db_denydatawriter permissions.
For the Team Leads, I have not come up with anything bullet-proof. If I
give db_ddladmin rights, it allows them to modify data regardless of any
explicit deny permissions I put on any objects.
Does anyone have any suggestions?
Thanks,
David Grau
Database Administrator
Surprise & DelightDavid
1. Create ITManagers Group and add it to sysadmin server role.
2. Create TeamLead Group
a) Don't make it a member of sysadmin server role
b) GRANT CREATE TABLE ,CREATE Function ,GRANT CREATE Procedure TO
TeamLead
Take a look at creating Roles in the BOL as well
"David Grau" <DavidGrau@.discussions.microsoft.com> wrote in message
news:D265D1AC-C3D5-408B-8C77-B91696442F9F@.microsoft.com...
> Hello All,
> I have a need to set up database security on our QA and Production servers
> in the following manner:
> IT Managers - Read/write access. Ability to view/start/stop scheduled
> jobs
> not owned by them (all jobs are owned by sa).
> Team Leads - Allow them to create/drop/alter stored procedures and
> functions
> only. Otherwise, read-only access to all other objects
> Developers - Read-only access to all objects.
> For the IT Managers, I have a couple of options. 1) Give dbo permissions,
> which will give them everything but the ability to view/start/stop jobs.
> I
> won't give them sysadmin rights.
> For the Developers, it's pretty easy. db_datareader permissions,
> db_denydatawriter permissions.
> For the Team Leads, I have not come up with anything bullet-proof. If I
> give db_ddladmin rights, it allows them to modify data regardless of any
> explicit deny permissions I put on any objects.
> Does anyone have any suggestions?
> Thanks,
> David Grau
> Database Administrator
> --
> Surprise & Delight|||Thanks for your reply. However, let me add more detail now that I know more
about this request.
The IT Managers want to have SQL Logins that have expanded security beyond
their Windows logins. Is there a way to give them read/write to each
database as well as the ability to start/stop/delete scheduled jobs?
Similarly, Team Leaders want separate SQL Logins that they can use that have
the following: read-only access to the databases; create/drop/alter stored
procedures and functions. No other abilities for the Team Leaders. They
should not be able to create/alter/drop tables or any other objects.
Can all this be accomplished through database roles?
Thanks,
David Grau
--
Surprise & Delight
"Uri Dimant" wrote:

> David
> 1. Create ITManagers Group and add it to sysadmin server role.
> 2. Create TeamLead Group
> a) Don't make it a member of sysadmin server role
> b) GRANT CREATE TABLE ,CREATE Function ,GRANT CREATE Procedure TO
> TeamLead
>
> Take a look at creating Roles in the BOL as well
>
> "David Grau" <DavidGrau@.discussions.microsoft.com> wrote in message
> news:D265D1AC-C3D5-408B-8C77-B91696442F9F@.microsoft.com...
>
>|||David
> The IT Managers want to have SQL Logins that have expanded security beyond
> their Windows logins. Is there a way to give them read/write to each
> database as well as the ability to start/stop/delete scheduled jobs?
Add them to sysadmin server role
"David Grau" <DavidGrau@.discussions.microsoft.com> wrote in message
news:3079CDD8-49BC-4466-9FC2-2CED09181265@.microsoft.com...[vbcol=seagreen]
> Thanks for your reply. However, let me add more detail now that I know
> more
> about this request.
> The IT Managers want to have SQL Logins that have expanded security beyond
> their Windows logins. Is there a way to give them read/write to each
> database as well as the ability to start/stop/delete scheduled jobs?
> Similarly, Team Leaders want separate SQL Logins that they can use that
> have
> the following: read-only access to the databases; create/drop/alter
> stored
> procedures and functions. No other abilities for the Team Leaders. They
> should not be able to create/alter/drop tables or any other objects.
> Can all this be accomplished through database roles?
> Thanks,
> David Grau
> --
> Surprise & Delight
>
> "Uri Dimant" wrote:
>

Database Permissions

We have a system at work that copies (using DTS) over databases from
one SQL Server box to another every night. The copying process drops
each target object and then recreates them. On the 2nd SQL Server box
I have an account setup that is supposed to only have access to run
queries on the databases that get copied over every night. However,
because the DTS packages are dropping the objects first we are losing
the table level permissions for this user, so this user can't access
these databases the next day. Is there a way to automate resetting
these permissions on each table in the databases? Perhaps I should be
using replication as opposed to DTS packages for copying over entire
databases? If I used replication, would this avoid losing the
permissions that I need?

Thanks,

Jeffjeffpuro@.yahoo.com (Jeff) wrote in message news:<7851a310.0401151322.4b8cf2e7@.posting.google.com>...
> We have a system at work that copies (using DTS) over databases from
> one SQL Server box to another every night. The copying process drops
> each target object and then recreates them. On the 2nd SQL Server box
> I have an account setup that is supposed to only have access to run
> queries on the databases that get copied over every night. However,
> because the DTS packages are dropping the objects first we are losing
> the table level permissions for this user, so this user can't access
> these databases the next day. Is there a way to automate resetting
> these permissions on each table in the databases? Perhaps I should be
> using replication as opposed to DTS packages for copying over entire
> databases? If I used replication, would this avoid losing the
> permissions that I need?
> Thanks,
> Jeff

You could script the table permissions, then execute that script after
copying the objects. Although it's not clear from your post why you
always drop and recreate the objects - DTS can copy only the data, or
you can put it in staging tables first, then insert into the final
production tables if you have some mapping logic. Replication would be
an option (perhaps snapshot replication), but it can be complex.

Simon