October 16, 2008 at 1:09 pm
I would like like to use Windows domain groups instead of Windows domain users, but I'm having difficulty getting this to work.
Background: I have set up several stored procedures and two database roles. Database role #1 has "execute" privileges for about half of the stored procedures, those that are read-only lookups of various data. Database role #2 has "execute" privileges for the other half of the stored procedures, those that are involved in inserting/updating data.
Windows authentication is being used for all logins.
The following process works:
1. Set up Windows domain users as Logins in SQL Server 2005.
2. Assign these logins to having "MyDatabase" as their default database.
3. Map this login as a user of "MyDatabase".
4. Assign the database user as a role member to either Database role #1 or both database roles, depending on their need for access.
5. Wrap the SQL Server connection and stored procedure calls in external software code
6. Run the external application
The following process does NOT work:
1. Set up Windows domain groups A, B, C, etc. which include the various users who will access SQL Server. These are the same Windows domain users I had set up with individual Logins above. Now, however, I remove all the individual Logins and database users from SQL Server.
2. Follow the five steps above exactly, except substituting "Windows domain group" for "Windows domain user", for both Login and database user.
3. Failure occurs at the final step (step #6 above) - the external application succeeds in making a connection, but the stored procedure cannot be accessed, returning the following message: "Connectivity error: ...... Could not find stored procedure 'schema_name.procedure_name'
If I add the Windows domain user back in, and run the application as that user, everything again works.
Insight regarding this would be much appreciated,
Thanks,
Randy
October 16, 2008 at 1:24 pm
In SQL 2000, MS said that best practice was to:
- Create a Global Group,
- Put Windows users in the Global Group,
- Put that Global Group in a Local Group on the server,
- Give the Local Group SQL permissions
This is called AGLP (Account->Global group->Local group->Permissions)
I assume it's still BP in 2005 ? Is that what you have ? Or are you assigning SQL permissions directly to the Global Group ?
October 16, 2008 at 1:43 pm
I'm working on this in conjunction with our network administrator, as handling Windows security is fairly new to me.
I don't know what you mean by the "BP" in your question "I assume it's still BP in 2005?".
The Windows domain groups I referred to, I don't know if they're "Global", but I assume they are, since they're visible across our entire network. These groups are not additionally set up as local groups on the SQL Server PC. Is that what you're suggesting that I should do?
I set up SQL Server logins that matched the Windows domain user logins, then set up those logins as users of my database, and finally set up those users as role members of the database role which has execute privileges for the stored procedures. All of this was from within SQL Server; I didn't set up anything in Windows on the SQL Server PC. This works for associating Windows (global?) domain users, but seems to have some limitations with Windows (global?) domain groups.
October 16, 2008 at 1:50 pm
homebrew01 (10/16/2008)
In SQL 2000, MS said that best practice was to:- Create a Global Group,
- Put Windows users in the Global Group,
- Put that Global Group in a Local Group on the server,
- Give the Local Group SQL permissions
This is called AGLP (Account->Global group->Local group->Permissions)
I assume it's still BP in 2005 ? Is that what you have ? Or are you assigning SQL permissions directly to the Global Group ?
Do you have a link to any documentation where this has been published as best practice for SQL Server? I would like to review that for myself - since, as far as I can recall, it has been an AGDRP (Accounts, Groups, Database Role, Permissions) model for SQL Server.
Anyways, I think the OP is running into an issue with the default schema which cannot be defined for a Windows Group. If that is the case, then you need to specifically grant access to the appropriate schema and objects and also specify the schema in all calls.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 16, 2008 at 2:05 pm
Do you have a link to any documentation where this has been published as best practice for SQL Server? I would like to review that for myself - since, as far as I can recall, it has been an AGDRP (Accounts, Groups, Database Role, Permissions) model for SQL Server.
I think we're both correct. More than one way to set things up:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sp3sec01.mspx#E1C
BP= "Best practice"
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply