bigator

Analyzing DEFAULT schema & assigned Role for a Database

Hi All,

Following SQL query will list available roles in database and the default schema they are assigned to.
You can also check when it’s last modified.

–Query Start
Use <Your Database Name>
Go

SELECT
DEFAULT_SCHEMA_NAME AS [DEFAULT SCHEMA NAME],
TYPE_DESC AS [ROLE DESCRIPTION],
[NAME] AS [ASSIGNED ROLE OR USER],
CREATE_DATE AS [SCHEMA CREATION DATE],
MODIFY_DATE AS [SCHEMA MODIFICATION DATE]
FROM SYS.DATABASE_PRINCIPALS
ORDER BY
MODIFY_DATE DESC
GO
–Query End

There are also other fields/columns in SYS.DATABASE_PRINCIPALS table;
which you can use somewhere, so here is the detailed description of its columns.

Column name Data type Description
name sysname Name of principal, unique within the database.
principal_id int ID of principal, unique within the database.
type char(1) Principal type:
S = SQL user
U = Windows user
G = Windows group
A = Application role
R = Database role
C = User mapped to a certificate
K = User mapped to an asymmetric key
type_desc nvarchar(60) Description of principal type.
SQL_USER
WINDOWS_USER
WINDOWS_GROUP
APPLICATION_ROLE
DATABASE_ROLE
CERTIFICATE_MAPPED_USER
ASYMMETRIC_KEY_MAPPED_USER
default_schema_name sysname Name to be used when SQL name does not specify schema. Null for principals not of type S, U, or A.
create_date datetime Time at which the principal was created.
modify_date datetime Time at which the principal was last modified.
owning_principal_id int ID of the principal that owns this principal. All principals except Database Roles must be owned by dbo.
sid varbinary(85) SID (Security Identifier) if the principal is defined external to the database (type S, U, and G). Otherwise, NULL.
is_fixed_role bit If 1, then this row represents an entry for one of the fixed database roles: db_owner, db_accessadmin, db_datareader, db_datawriter, db_ddladmin, db_securityadmin, db_backupoperator, db_denydatareader, db_denydatawriter.

Trusted Source: http://msdn.microsoft.com/en-us/library/ms187328.aspx

Thanks, Khilit

bigatorFebruary 14, 2012 · bigatorKhilitchandra Prajapati · bigatorOne Comment
bigatorTags: , ,  · Posted in: General, SQL Server, Tutorial

One Response

  1. toronto seo small business - February 26, 2012

    Hello, Neat post. There is an issue along with your website in web explorer, could test this… IE still is the marketplace leader and a large element of other folks will pass over your magnificent writing because of this problem.

Leave a Reply


%d bloggers like this:
bigator