question

Sri 1 avatar image
Sri 1 asked

issue with user login permission on database

i have given a user with datareader/datawriter/execute permission on a database. still then user is able to see procedure as locked icon and more over can't run sp_helptext.. what is issue here what more permission or things i can do for that user.. looks issue is with something else.. help..!
permissionshelp
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image
Usman Butt answered
For a user to see the definition of **user objects**, it must have any one of the following permissions: ALTER, CONTROL, TAKE OWNERSHIP, or VIEW DEFINITION. I guess in your case you should give VIEW DEFINITION permission. Cheers.
10 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sri 1 avatar image Sri 1 commented ·
i think that will not work as objects are encryted..
0 Likes 0 ·
Sri 1 avatar image Sri 1 commented ·

SELECT SCHEMA_NAME(sp.schema_id) AS [Schema],
    sp.name AS [Name],
    sp.object_id AS [ID],
    sp.create_date AS [CreateDate],
    sp.modify_date AS [DateLastModified],
    CAST(CASE WHEN smsp.definition IS NULL THEN 1 ELSE 0 END AS bit) AS [IsEncrypted]
FROM sys.all_objects sp LEFT JOIN sys.sql_modules smsp 
    ON smsp.object_id = sp.object_id
WHERE smsp.definition IS NULL -- This identifies an encrypted object
    AND sp.type IN ('FN', 'IF', 'V', 'TR', 'PC', 'TF', 'P')
    AND sp.is_ms_shipped = 0 
SELECT sp.type, sp.type_desc
    , COUNT(smsp.definition) AS UnencryptedObjects -- only non-null or unencrypted objects will be counted
    , COUNT(*)-COUNT(smsp.definition) AS EncryptedObjects
    , COUNT(*) AS Total
FROM sys.all_objects sp LEFT JOIN sys.sql_modules smsp 
    ON smsp.object_id = sp.object_id
WHERE sp.type IN ('FN', 'IF', 'V', 'TR', 'PC', 'TF', 'P')
    AND sp.is_ms_shipped = 0
GROUP BY sp.type, sp.type_desc
--- so milliion dollar question in my mind is, if user A can see the script as i myself tried his login and not seeing any lock symbol and can easily pull script/ why there is a issue with USER B as his login showing lock on object as well getting error on pulling scrpt.
0 Likes 0 ·
Sri 1 avatar image Sri 1 commented ·
as per Pavel if it is encryted, no one can see the script..
0 Likes 0 ·
Usman Butt avatar image Usman Butt commented ·
Can you please let us know that there is no 3rd party tool in installed? and there is no more details you can add?
0 Likes 0 ·
Sri 1 avatar image Sri 1 commented ·
one user is master account from which i am using and another is a simple user. but question is now that if above script showing all are encryrted, why one user can see script and other can't and see lock symbol.
0 Likes 0 ·
Show more comments
Pavel Pawlowski avatar image
Pavel Pawlowski answered
to use a sp_helptext on an object you need to hvae ALTER, CONTROL, TAKE OWNERSHIP, or VIEW DEFINITION permission on that object. So if you have only datareader/datawriter/execute permission, than you will not be able to view the definition of the object. The lock icon could also signal, that the object is encrypted. For such encrypted objects you will not eb able to view the definition even you have the above mentioned permissions.
9 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sri 1 avatar image Sri 1 commented ·
but i have different user which is able to see the defination... y so...
0 Likes 0 ·
Pavel Pawlowski avatar image Pavel Pawlowski commented ·
So as me and @Usman Butt stated, the user needs the ALTER, CONTROL, TAKE OWNERSHIP, or VIEW DEFINITION permission on that object. In case the other user can see the definition, then he has one of the mentioned permissions assigned directly or have effective permissions inherited.
0 Likes 0 ·
Sri 1 avatar image Sri 1 commented ·
yes you are right objects are encrypted... so is there any way and what other possiblility now.. i just confused now..pls suggest..
0 Likes 0 ·
Usman Butt avatar image Usman Butt commented ·
This is very strange. Are you sure you or any other user can see the definition of encrypted stored procedure?
0 Likes 0 ·
Sri 1 avatar image Sri 1 commented ·
@usman.. sry my fault... from user login which can see having ddladmin and db_owner.. that is why he can see...... @Pavel and @Usman, so is there any other thing that i can do..to let other user can see defination or i have to give db_owner and ddlAdmin permission to see encrypted script... which can be very very dangerous...
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.