September 22, 2008 at 12:33 pm
Hey all,
Hopefully you can help, cause I have hit a mental brick wall.
Set the scene:
We have several environemts (vm's) and each one has a SQL2005SP2 64-bit server. We have a database in an environment which recently had a release (giving it more table, functions, etc).
The database was moved into another environment (for UAT) but a required function fails due to permissions. There is no error recorded in the server log and the app is CADIS and doesnt give any userful hints.
Running an fn_MyPermissions check (dont have the script here will upload tomorrow) on the environemnt that works shows lots and lots of permissions (almost dbo like), create table, index, delete, reference (not what i would expect). Running the same script on the environment that doesnt work shows less permissions and more of the type i would expect.
The permissions are all gained via AD groups (which relate to SQL group logins), there are then db group roles (within the db) which then set further db roles (depending on the group).
The question:
Ok, so I need (urgently) to work out what is different. Either the env that works is correct and the other one wrong, in which case i need to find out what the difference is. OR, the one that doesnt work is wrong, the dev guys have made a mistake in which case i still need to work out the difference and rectify it.
It looks to me that the extra rights are coming from a server role (type permission), but non evident in GUI.
Can you help? All suggestions welcome.
Thanks.
Adam Zacks-------------------------------------------Be Nice, Or Leave
September 22, 2008 at 12:50 pm
If you've not disabled builtin\administrators I'd look there.
Lots of times development groups put themselves as local admins on their server because it's less headaches. It's not an obvious place to look either because you're probably focusing on AD and SQL Server instead of a local group.
another trick I use in SQL 2005 is the
EXECUTE AS LOGIN = 'DOMAINUSER\ACCOUNT'
--RUN your stored procs or whatever
REVERT
This sometimes helps me pin down exactly what they don't have access to so I can grant it.
Craig Outcalt
September 23, 2008 at 3:54 am
The code I run to check permissions is
use [cadis_release]
SELECT *
FROM fn_my_permissions('cadis_release', 'DATABASE');
GO
(ran as the user). It seems that some how the user is getting dd_ladmin rights from somewhere.... But they are specific to that server/domain (as they arent effective in the other environemt).
Thanks in advance.
Adam Zacks-------------------------------------------Be Nice, Or Leave
September 23, 2008 at 10:26 am
all sorted now. thanks for your help. It turned out that the database dbo had been set to a group which mapped to an AD group which was membered by 'Domain Users' :w00t:
Adam Zacks-------------------------------------------Be Nice, Or Leave
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply