November 5, 2008 at 8:39 am
Hi,
This may be really simple and apologies if I am asking a dumb question but here goes anyway.
We have a 3rd party application and database that sits on a SQL Server 2005 Std 64bit installation on Server 2003 r2. The SQL Server runs approx 50 databases, including a test and live db for the test and live application in question.
There are problems moving some developments from test to live and the vendors has asked that I give VIEW DEFINITION to Public at Server level (ie R Click server|Properties|Permissions - Add Public and then tick View any definition)
I am reluctant to do this as the server has HR and Financial databases along with other business confidential data and giving any privilege to Public goes against my training/experience/instinct what have you.
I have run a script on the database as
USE [MYDB]
GO
GRANT VIEW DEFINITION TO Public
and this has not resolved the problem with the app
If I follow their request I would effectively be running
USE [master]
GO
GRANT VIEW ANY DEFINITION TO Public
wouldnt I !?
If so, what is the difference within the database in question? Isnt the above the same as running the first script in each database on the server in turn?
As part of the research on this I have run sp_helpprotect @username='Public' and this brings back certain results including :
...
OwnerObjectGranteeGrantorProtectTypeActionColumn
..publicdboGrant VIEW DEFINITION.
.
In BOL though it advises to move to the SQL 2005 system views - sys.database_principals, sys.database_permissions and sys.objects.
When i run the script
SELECT s.name 'Schema' ,
o.name Object ,
dp2.name username ,
dp.type permissions_type ,
dp.permission_name ,
dp.state permission_state ,
dp.state_desc
FROM sys.database_permissions AS dp
JOIN sys.objects AS o ON dp.major_id = o.object_id
JOIN sys.schemas AS s ON o.schema_id = s.schema_id
JOIN sys.database_principals as dp2 ON dp.grantee_principal_id = dp2.principal_id
WHERE dp2.name = 'public'
I get zero rows returned. Is it something to do with the inclusion of sys.objects and sys.schemas?
Can anyone point me back on track please? I want to be able to run a script that I can trust to give me a comprehensive statement of the access that any user has to any database either as a user or a member of a role.
TIA
November 5, 2008 at 10:10 am
I want to be able to run a script that I can trust to give me a comprehensive statement of the access that any user has to any database either as a user or a member of a role.
Check this script, it might be useful for this purpose: http://www.sqlservercentral.com/scripts/Administration/63841/
If all the user objects are tied to DBO schema, you should be able to use the below script. This by in itself would exclude the objects belonging to SYS schema.
USE [DATABASENAME]
GO
GRANT VIEW DEFINITION ON SCHEMA::[dbo] TO
GO
Kindest Regards,
Shivaram Challa
(http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
Forum Etiquette: How to post data/code on a forum to get the best help[/url]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply