Permissions within groups

Running SQL Server 2000 under Windows 2003 server. All
updates and service packs applied. Using Windows
Authentication via group membership. No individual users
are defined in SQL Server. Some users can update tables,
while other users in the same group cannot (permission
denied).
I'm baffled...These people are probably members of another group that has been denied
those rights. Remember that permissions are cumulative except that deny
trumps...
"Jay Varner" <dimsjay@.dims-vote.com> wrote in message
news:e55901c3f0e8$da00f2c0$a301280a@.phx.gbl...
> Running SQL Server 2000 under Windows 2003 server. All
> updates and service packs applied. Using Windows
> Authentication via group membership. No individual users
> are defined in SQL Server. Some users can update tables,
> while other users in the same group cannot (permission
> denied).
> I'm baffled...|||Try comparing users that can update vs those that can't using gpresult.
321709 HOW TO: Use the Group Policy Results Tool in Windows 2000
http://support.microsoft.com/?id=321709
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||I appreciate your quick reply, Don, but am not sure I expressed the
problem sufficiently.
Using Active Directory in W2K3 server, we defined specific user groups
for each of three domains that need to access the database. Each user
appears in only one group. All of these groups have been added to SQL
Server, and all have the same rights to the database(s). The problem
seems to occur for several people in each group, and does not seem to be
related to any permissions they are allowed on the domain. For
instance, in one of the groups, one user is a standard DOMAIN USER in
Windows, and he is able to perform updates to any of the tables in the
database; another user is a DOMAIN ADMIN who belongs to the same group,
but he is denied access to perform updates.
If we assign the group SYSTEM ADMINISTRATOR priveleges on the database,
it seems to resolve the problem, but thats not an acceptable resolution
for this large office.
If we assign each individual user (rather than the groups) to SQL, the
problem goes away. Again, this is a large office, and they would like
to avoid the additional overhead of having to add each new user to both
Windows and SQL Server.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!

Permissions with Windows groups and view to other databases

I am having a problem with permissions using Windows groups. I have a database (database1) that has permissions granted via Windows groups. Two groups (group1 and group2) are members of the db_datareader role in database1, and this work fine. Do to the number of tables that get created during our work, using db_datareader is the easiest way to keep up with permissions without creating a maintenance problem. Now I have a table that I want to add to this database, but I only want group2 to have select permission on this one table which is a problem because group1 has the db_datareader role. So I thought I could create a view in this database to the restricted table that I put in database2. Then in database2 I only added group2 as a user with the permission to select from this table. Unfortunately the group membership does not seem to get interpretted correctly in database2 and no one can successfult select from the view in database1.

In other words, user1 who belongs to group1 connects to database1 and cannot select from the restricted view -- this is what I would expect. However, when user2 who belongs to group2 connects to database1 they also cannot select from the restricted view -- not the behvior I would expect. Now, if I make user2 a user in database2 with select on the restricted table then user2 can connect to database1 and successfuly get data from the restricted view. So it looks like the fact that user2 belongs to group2 is never passed to database2 via the select from the view on database1. Is this indeed the way that Windows group security is working or is meant to work in SQL Server?

I realize I could solve this simplified version of the problem by creating my own role in database1 for group1 etc., but I am trying to solve a bigger problem in our environment that has hundreds of databases across numerous servers.

Thanks

Rob

Why not simply deny SELECT permission to group1 for that particular table? The rule that you need to remember is that a deny will trump a grant, so the deny will take precedence over the db_datareader membership.

Thanks

Laurentiu

|||Well, that won't quite work. The two Windows groups we are talking about have some overlapping members, but one group is not a complete subset of the other. So if I deny SELECT to group1 then some people that I want to access the data (group2) because they are in both groups and as you point out deny has a higher priority. Is there any reason group permissions are not valid in database2 when selecting from the view in database1?|||

This is the expected behavior; but it sounds like you may be trying to attempt cross-database ownership chaining (also known as CDOC, look for “Using ownership chains” topic in BOL). CDOC is a feature that is disabled by default and we recommend against using it because of the security risks inherent from this feature. For more information on CDOC look for “Using ownership chains” topic in BOL.

The reason why user2 is failing to access the table is that there is a separate user token for database1 and database2 (both derived from the same Windows login token). On database1 the user2 token will look similar to this:

Primary identity:

· user2, Windows user

Secondary identities:

· group2, Windows group

· db_datareader, role

When accessing the view, the permissions are checked against this token, and they will succeed, but the view is making reference to database2.<some_schema>.restricted_table, therefore it is necessary to create a token for database2.

On your first attempt (without creating a user in Database2, and granting permission to access the table) the user token creation process for database2 should have failed with a “user cannot access this database”-type of error.

Here are a few potential workarounds that may help you:

Instead of using db_datareader you can use different schemas and grant SELECT based on the schemas to differentiate groups, for example:

GRANT SELECT ON SCHEMA::[Schema_group1] TO group1

GRANT SELECT ON SCHEMA::[Schema_group2] TO group2

GRANT SELECT ON SCHEMA::[Schema_all] TO group1, group2

That way the SELECT permission would be restricted to only the schemas you defined.

Another alternative for cross-DB access could be using signatures, similar to the one I described in the following article: http://blogs.msdn.com/raulga/archive/2006/10/30/using-a-digital-signature-as-a-secondary-identity-to-replace-cross-database-ownership-chaining.aspx

Please, let us know if any of this alternatives worked for you or if you have any additional questions.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

Permissions with Windows groups and view to other databases

I am having a problem with permissions using Windows groups. I have a database (database1) that has permissions granted via Windows groups. Two groups (group1 and group2) are members of the db_datareader role in database1, and this work fine. Do to the number of tables that get created during our work, using db_datareader is the easiest way to keep up with permissions without creating a maintenance problem. Now I have a table that I want to add to this database, but I only want group2 to have select permission on this one table which is a problem because group1 has the db_datareader role. So I thought I could create a view in this database to the restricted table that I put in database2. Then in database2 I only added group2 as a user with the permission to select from this table. Unfortunately the group membership does not seem to get interpretted correctly in database2 and no one can successfult select from the view in database1.

In other words, user1 who belongs to group1 connects to database1 and cannot select from the restricted view -- this is what I would expect. However, when user2 who belongs to group2 connects to database1 they also cannot select from the restricted view -- not the behvior I would expect. Now, if I make user2 a user in database2 with select on the restricted table then user2 can connect to database1 and successfuly get data from the restricted view. So it looks like the fact that user2 belongs to group2 is never passed to database2 via the select from the view on database1. Is this indeed the way that Windows group security is working or is meant to work in SQL Server?

I realize I could solve this simplified version of the problem by creating my own role in database1 for group1 etc., but I am trying to solve a bigger problem in our environment that has hundreds of databases across numerous servers.

Thanks

Rob

Why not simply deny SELECT permission to group1 for that particular table? The rule that you need to remember is that a deny will trump a grant, so the deny will take precedence over the db_datareader membership.

Thanks

Laurentiu

|||Well, that won't quite work. The two Windows groups we are talking about have some overlapping members, but one group is not a complete subset of the other. So if I deny SELECT to group1 then some people that I want to access the data (group2) because they are in both groups and as you point out deny has a higher priority. Is there any reason group permissions are not valid in database2 when selecting from the view in database1?|||

This is the expected behavior; but it sounds like you may be trying to attempt cross-database ownership chaining (also known as CDOC, look for “Using ownership chains” topic in BOL). CDOC is a feature that is disabled by default and we recommend against using it because of the security risks inherent from this feature. For more information on CDOC look for “Using ownership chains” topic in BOL.

The reason why user2 is failing to access the table is that there is a separate user token for database1 and database2 (both derived from the same Windows login token). On database1 the user2 token will look similar to this:

Primary identity:

· user2, Windows user

Secondary identities:

· group2, Windows group

· db_datareader, role

When accessing the view, the permissions are checked against this token, and they will succeed, but the view is making reference to database2.<some_schema>.restricted_table, therefore it is necessary to create a token for database2.

On your first attempt (without creating a user in Database2, and granting permission to access the table) the user token creation process for database2 should have failed with a “user cannot access this database”-type of error.

Here are a few potential workarounds that may help you:

Instead of using db_datareader you can use different schemas and grant SELECT based on the schemas to differentiate groups, for example:

GRANT SELECT ON SCHEMA::[Schema_group1] TO group1

GRANT SELECT ON SCHEMA::[Schema_group2] TO group2

GRANT SELECT ON SCHEMA::[Schema_all] TO group1, group2

That way the SELECT permission would be restricted to only the schemas you defined.

Another alternative for cross-DB access could be using signatures, similar to the one I described in the following article: http://blogs.msdn.com/raulga/archive/2006/10/30/using-a-digital-signature-as-a-secondary-identity-to-replace-cross-database-ownership-chaining.aspx

Please, let us know if any of this alternatives worked for you or if you have any additional questions.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

Permissions for Domain user account

Hi,
I want to use a domain user account not belonging to local admin or domain admin groups in SQL 2000/2005 Enterprise edition. This is what I've done so far..
On the machine that is the Domain Controller:
- installed SQL 2005 as a domain admin

- created a domain user account using Active Directory Users and Computers. This user is only

"Member of" domain users; not any Administrators group.

- added this user to SQL Server Management Studio->Logins and in Server Roles assigned

sysadmin role.
Question 1: Do I need to give any additional permissions to this user to work with SQL?
Question 2: How can I test this user for basic SQL operations like database creation? Can I use Osql?
Question 3: Can I use this user account to login to my domain controller using remote desktop? I tried adding this user to remote users, but in vain.

Thanks!

Hi there,

Just a few of my thoughts....

Question 1: Do I need to give any additional permissions to this user to work with SQL?

Well, going off what you have said (making the domain user part of the sysadmin server role) this will allow the domain user to do any action on your database server.

Whether this is a good thing or not is debatable as it's pretty good practice to only give a user the lowest level of permissions they need in order to do whatever work they need to do. Not knowing your exact situation, giving the domain user the level of privilege you have might be overkill - but like I said it depends on what you're intending etc.

I would recommend you review security topics in SQL Server Books Online and use the info there in conjunction with your knowledge of the circumstances to select the appropriate strategy for giving permission to this user and any other users whose accounts you need to add.

Question 2: How can I test this user for basic SQL operations like database creation? Can I use Osql?

There are a few options here, I'll go into a few....

A) Yes you can use the OSQL or iSQL command line utilities to execute T-SQL if you're working with SQL Server 2000. Once again, SQL Server Books Online is a good resource for this.

If you're working with SQL Server 2005, it's better to use sqlcmd if you're looking to do things via a command line (http://msdn2.microsoft.com/en-us/library/ms170207(SQL.90).aspx)

B) It's easier to use graphical tools, I think. If you can use something like Query Analyzer (SQL Server 2000) or Management Studio (SQL Server 2005) to connect to your DB as the domain user (e.g. logging onto a machine with those tools installed as the domain user and then using those tools to connect to your database server) then that would probably be easier

Question 3: Can I use this user account to login to my domain controller using remote desktop? I tried adding this user to remote users, but in vain.

From what you've written it seems like you're doing the right thing. Here's a tutorial on the complete process which may help: http://www.windowsnetworking.com/articles_tutorials/Windows_2003_Terminal_Services_Part1.html
(Note that it's split into two parts...The link I've included is to the first part but the first part connects to the second part via another link)

TechNet isn't bad either: http://technet2.microsoft.com/windowsserver/en/technologies/featured/termserv/default.mspx

Like I said, from what you've written it's a little hard to gauge the exact problem but it seems like you're doing the right things. However, if you want to run through the steps in the articles & stuff I've placed links to above then things should work for your Terminal Services setup (they did for me)

========

Hope this helps a bit
|||

Thanks alot for your help. Sorry for a delayed reply as I was reading the material you suggested and the good news is that I am able to implement the same and have got things working.
ref - Question 1: As for the permissions, as you suggested, I no longer give the user sysadmin role. The user has dbowner role and it suffices.
ref-Question 2: I used Query Analyser itself by logging in to the machine as the domain user.
ref- Question 3: I could not get Remote Desktop to work for the user. I guess some licensing issue. As a workaround, I used VNC to login as the domain user.

Thanks again.

permissions

What is the difference between managing permissions through users and groups
or through user-defined roles?Hello,
We can create Microsoft SQL Server database roles when a group of users
needs to perform a specified set of activities in SQL Server and one of the
following is true:
* There is no applicable Microsoft Windows NT 4.0 or Windows 2000 group.
* You do not have permissions to manage Windows NT 4.0 or Windows 2000 user
accounts.
Note Avoid deep levels of nested roles because this can affect performance.
For more informaton, refer to the following topic in SQL server books
online:
"Creating User-Defined SQL Server Database Roles"
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad
_security_6x5x.asp
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
true and you can take advantage of Roles while have to set permissions for
numerous users e.g you have to assign permisson for sales
,marketing,packaging,acount,it and admin deprtment and all dept # 50 users
then if you will assign individualy permisions it will be headache / hectic
but if you create a Role on based of Department's needed privillage then you
have to just map a user account with those Role and its done . more over if
you use your Windows User Account rather SQL it will be more secure..
HTH
Regards
--
Andy Davis
Active Crypt Team
---
SQL Server Encryption Decryption Software
http://www.activecrypt.com
"docsql" wrote:
> What is the difference between managing permissions through users and groups
> or through user-defined roles?
>
>

Permission Issues running DTS from Agent

We have a DTS which loads files using FTP to another
server. This will be used by app groups and will be run
via click of button using sp_start_job,

Somehow the userid running the job gets "not sysadmin to
run cmdshell.." Top fix that, we granted it to execute the
xp_cmdshell and also created a proxy account for SQL Agent
which is an admin on server and sql. However, still we get
permission errors like "...The needed permission is
missing to run command shell.." Please help . The DTS is
being called from SQl Agent job and we don't want an admin
ID to be used .Do you have 'exec master.dbo.' in from of xp_command... :confused: