Table script

Transact-SQL
1

Hello,
I was just shown the script behind a table and I wanted to access however when I tried to see myself using "script table as" and "create to", I couldn't see the same script. Would the script be kept in a stored procedure?
Am no longer connected myself to check! :frowning:

Thanks in advance

2

I would suspect that your user does not have the VIEW DEFINITION permission on that database.

3

Thanks @StephenChapman, is there a way I can check that is the case please?

4

What kind of script was it?
a DML script or DDL Script?

5

Here's a script to check a user's permissions

 WITH    perms_cte as
(
        select USER_NAME(p.grantee_principal_id) AS principal_name,
                dp.principal_id,
                dp.type_desc AS principal_type_desc,
                p.class_desc,
                OBJECT_NAME(p.major_id) AS object_name,
                p.permission_name,
                p.state_desc AS permission_state_desc
        from    sys.database_permissions p
        inner   JOIN sys.database_principals dp
        on     p.grantee_principal_id = dp.principal_id
)
--role members
SELECT rm.member_principal_name, rm.principal_type_desc, p.class_desc, 
    p.object_name, p.permission_name, p.permission_state_desc,rm.role_name

--INTO #t
FROM    perms_cte p
right outer JOIN (
    select role_principal_id, dp.type_desc as principal_type_desc, 
   member_principal_id,user_name(member_principal_id) as member_principal_name,
   user_name(role_principal_id) as role_name
    from    sys.database_role_members rm
    INNER   JOIN sys.database_principals dp
    ON     rm.member_principal_id = dp.principal_id
) rm

ON     rm.role_principal_id = p.principal_id
where rm.member_principal_name like 'your userid here'
order by 1
1 Like
6

I think it was DDL (we were looking into an issue and it was done quickly). Thanks