SQL Table Permissions Query

I'm installing the EOne product SmartList Builder and it's not working quite right, and I suspect a SQL permission issue. Normally tables in Dynamics have permissions granted to the DYNGRP user

I checked a table or two, and yep, no permissions. So I sat down to write a SQL query that would show all tables in the database with no permissions.

Then, I wrote a script to add the needed perms to the tables.

Both queries are below.

Enjoy.                       

Related Articles

... and you 'll find more on the SQL (General) Menu

 

 

select o.name, t.name
    from sys.objects o
        left join (
            select perm.major_id, roleprinc.name
                from sys.database_permissions perm
                    left join sys.database_principals roleprinc on perm.[grantee_principal_id] = roleprinc.[principal_id]
                group by perm.major_id, roleprinc.name
            ) t on   o.object_id =t.major_id
    where o.type = 'u'
    order by 2,1

                       

declare @sql varchar(200)
DECLARE @TABLENAME VARCHAR(255)
   
DECLARE curName CURSOR LOCAL FAST_FORWARD FOR
    select o.name
        from sys.objects o
            left join (
                select perm.major_id, roleprinc.name
                    from sys.database_permissions perm
                        left join sys.database_principals roleprinc on perm.[grantee_principal_id] = roleprinc.[principal_id]
                    group by perm.major_id, roleprinc.name
                ) t on   o.object_id =t.major_id
        where o.type = 'u'
            and t.name is null
        order by 1
   
OPEN curName
WHILE 1=1
BEGIN
    FETCH NEXT FROM curName INTO @TABLENAME
    if @@fetch_status <> 0 begin
        break
    end
 
    set @sql = 'grant insert on ' + @tablename + ' to DYNGRP  '
    exec (@sql)
   
    set @sql = 'grant update on ' + @tablename + ' to DYNGRP  '
    exec (@sql)
 
    set @sql = 'grant delete on ' + @tablename + ' to DYNGRP  '
    exec (@sql)
 
    set @sql = 'grant select on ' + @tablename + ' to DYNGRP  '
    exec (@sql)
END
CLOSE curName
DEALLOCATE curName

 

RealWorldCode gives developers practical, real‑world solutions with clean, working code — no fluff, no theory, just answers.
Links
Home
Knowledge Areas
Sitemap
Contact
Et cetera
Privacy Policy
Terms and Conditions
Cookie Preferences