How to determine Default Database Permissions

• HookSQLDBA7

  SSChampion

  Points: 10709

  More actions

  November 16, 2004 at 7:57 am

  #163277

  The Public Role captures default database permissions. How can I determine what are those default database permissions?

  Thanks in advance, Kevin

   

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736280

  More actions

  November 16, 2004 at 9:51 am

  #530302

  Select the public role, right click, properties, then permissions button.

• sa24

  SSCrazy Eights

  Points: 8027

  More actions

  November 16, 2004 at 9:56 am

  #530304

  select object_name(id),case action  when 26  then 'REFERENCES'

  when 178 then 'CREATE FUNCTION'

  when 193 then 'SELECT'

  when 195 then 'INSERT'

  when 196 then 'DELETE'

  when 197 then 'UPDATE'

  when 198 then 'CREATE TABLE'

  when 203 then 'CREATE DATABASE'

  when 207 then 'CREATE VIEW'

  when 222 then 'CREATE PROCEDURE'

  when 224 then 'EXECUTE'

  when 228 then 'BACKUP DATABASE'

  when 233 then 'CREATE DEFAULT'

  when 235 then 'BACKUP LOG'

  when 236 then 'CREATE RULE'

  end as permission , case protecttype when 204 then 'Grant_w_Grant'

   when 205 then 'Grant'

   when 206 then 'Revoke'

   end as type

   from sysprotects a join sysusers b on a.uid = b.uid and b.name = 'public'

  The above should give you all the permissions for public.

• stuartb

  SSC Veteran

  Points: 268

  More actions

  November 17, 2004 at 3:54 am

  #530450

  exec dbo.sp_helprotect null,'public'