September 9, 2014 at 12:33 am
Hi Everyone,
I have a Question i.e,
I want to create a login with some restriction like the following...
1.I will create a login and ll mapped to a particular DB with the Database Role 'db_datarerader' only,
2.We wants to display the all objects under a DB but we don't want to provide the View Definition to that particular Login.
3.If we Deny the View definition option he can't able to see the Objects which are there under the DB.
4.So My Clear Question is we want to display the Object like tables ,Sps...etc and we don't want to allow him to view the definition of those objects....
Can Any one help me out on this....
PFA.....
Regards
Chowdary...
Regards
Chowdary...
September 9, 2014 at 2:29 am
Awaiting for your Valuable Replies.......................:-)
Regards
Chowdary...
September 9, 2014 at 6:55 am
😎
Regards
Chowdary...
September 9, 2014 at 7:46 am
Did you get to look at permission granted to public role?
You could apply DENY on few sys schema objects.
Try few of the below, ensure it does not deny the users who needs access.
DENY EXECUTE ON SP_HELP TO public
DENY SELECT ON SYSCOLUMNS TO public
DENY SELECT ON SYSCOLUMNS TO _Testonetime -- _Testonetime is a test user/login
DENY SELECT ON SYS.COLUMNS TO _Testonetime
DENY SELECT ON SYS.all_columns TO _Testonetime
DENY SELECT ON SYS.all_OBJECTS TO _Testonetime
--This query would be helpful to see permission to role 'public'
SELECT
DatabaseName = DB_NAME()
,UserName=dpl.name
,ObjectSchemaName=OBJECT_SCHEMA_NAME(major_id,DB_ID())
,ObjectName=OBJECT_NAME(major_id)--,major_id
,GranteeName=USER_NAME(grantee_principal_id)
,Grantor_Name = SUSER_NAME(grantor_principal_id)
,dpr.permission_name
,dpr.state_desc
--,ao.type_desc
--,dpr.*
FROM SYS.database_permissions dpr
JOIN sys.database_principals dpl ON dpr.grantee_principal_id = dpl.principal_id
--JOIN sys.all_objects ao ON dpr.major_id = ao.object_id
WHERE dpl.name = 'public'
ORDER BY ObjectName ASC
September 9, 2014 at 10:38 pm
Hi Mr.Suneel,
Thank you for ur valuable reply,
I have Executed DENY SELECT ON SYS.all_OBJECTS TO XXXXX ,But after executing this he can't able to use SELECT cmd but he can able to generate the script by selecting Tblname-->Script Table as -->Create To.I we want to restrict that option to him what we have to do.
Regards
Chowdary....
Regards
Chowdary...
September 10, 2014 at 1:32 am
Once again thank you all for ur support...
Regards
Chowdary...
September 10, 2014 at 8:43 am
Chowdary's (9/9/2014)
DENY SELECT ON SYS.all_OBJECTS TO XXXXX ,But after executing this he can't able to use SELECT cmd but he can able to generate the script by selecting Tblname-->Script Table as -->Create To.
You could try this.
DENY EXECUTE ON SP_HELP TO _Testonetime
DENY SELECT ON SYSCOLUMNS TO _Testonetime -- _Testonetime is a test user/login
DENY SELECT ON SYS.COLUMNS TO _Testonetime
DENY SELECT ON SYS.all_columns TO _Testonetime
GRANT SELECT ON SYS.all_OBJECTS TO XXXXX
September 10, 2014 at 10:41 pm
Hi Suneel,
It worked for me,Now the User can able to see the Tables but not the definition of the Tables.
But the User can't able to see the SP's.
If i Login with SA i can able to view the SP's but with restricted login i cant able to see the SPs ,
If i want to allow him to see the only SP's name like same as Tables what can i do....
Regards
Chowdary.....
Regards
Chowdary...
September 11, 2014 at 10:03 am
I think you will need to give your user select access on sys.all_objects and deny select access on sys.syscolumns and sys.sql_modules for the user to get SP names but not definitions. You may want to deny select permission on some of sys.all_objects columns, depending on how restricted you want his information about objects in general to be.
Tom
September 12, 2014 at 1:51 am
Dear Tom,
I have given select access on sys.all_objects and deny select access on sys.syscolumns and sys.sql_modules for the user ,Now that user can able to get SP names but not the User defined SP only System SP are coming.
If I want to see the User defined SP along with the System SP's means what should i do.
And One more Question i have, The restricted user can able to see the Script Database by using the Script Database as --->Create To-->New Query Window ,If i want deny that what should i do.
Can you pls help me out...
Regards
Chowdary....:-)
Regards
Chowdary...
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply