Use SQL statements to obtain SQL Server database logon User Permissions

Returns a report that contains information about the user permissions or statement permissions of an object in the current database.

Syntax

Sp_helprotect [[@ name =] 'object _ statement ']
[, [@ Username =] 'security _ account']
[, [@ Grantorname =] 'grantor']
[, [@ Permissionarea =] 'type']

Parameters

[@ Name =] 'object _ statement'

Is the name of the object or statement in the current database to report its permissions. The data type of object_statement is nvarchar (776). The default value is NULL. This default value returns all object and statement permissions. If a value is an object (table, view, stored procedure, or extended stored procedure), it must be a valid object in the current database. The object name can contain the owner qualifier in the form of owner. object.

If object_statement is a statement, it can be:

CREATE DATABASE

 

CREATE DEFAULT

 

CREATE FUNCTION

 

CREATE PROCEDURE

 

CREATE RULE

 

CREATE TABLE

 

CREATE VIEW

 

BACKUP DATABASE

 

BACKUP LOG

 

[@ Username =] 'security _ account'

Is the name of the security account whose permissions are returned. The data type of security_account is sysname. The default value is NULL. This default value returns all security accounts in the current database. Security_account must be a valid security account in the current database. When you specify a Microsoft Windows NT user, specify the name that the Windows NT user can recognize in the database (use sp_grantdbaccess to add ).

[@ Grantorname =] 'grantor'

Is the name of an authorized security account. The data type of grantor is sysname, and the default value is NULL. This default value returns all the permissions granted by any security account in the database. When you specify a Windows NT user, specify the name that the Windows NT user can recognize in the database (use sp_grantdbaccess to add ).

[@ Permissionarea =] 'type'

Is a string that indicates whether the object permission (string o), statement permission (string s), or both are displayed (o s ). The data type of type is varchar (10), and the default value is o s. Type can be any combination of o and s, which can be between o and s or without commas or spaces.

Return code value

0 (successful) or 1 (failed)

Result set

Column name

Data Type

Description

Owner

Sysname

Name of the object owner.

Object

Sysname

Object Name.

Grantee

Sysname

The name of the user to which the permission is granted.

Grantor

Sysname

Name of the user authorized to the specified authorized user.

ProtectType

Char (10)

Protection Type name: GRANT
REVOKE

 

Action

Varchar (20)

Permission name: REFERENCES
SELECT
INSERT
DELETE
UPDATE
CREATE TABLE
CREATE DATABASE
CREATE FUNCTION
CREATE RULE
CREATE VIEW
CREATE PROCEDURE
EXECUTE
BACKUP DATABASE
CREATE DEFAULT
BACKUP LOG

 

Column

Sysname

Permission type: All = Permission applies to All the current columns of the object.
New = Permission applies to any new columns that can be modified on the object (using the alter statement) in the future.
The combination of all + new = All and new.

 

Note

All parameters in this process are optional. If sp_helprotect is executed without parameters, all permissions granted or denied in the current database are displayed.

If some parameters are specified, rather than all parameters are specified, use the named parameters to identify specific parameters, or use null as a placeholder. For example, to report all permissions granted to DBO, run the following command:

Exec sp_helprotect null, null, DBO

Or

Exec sp_helprotect @ grantorname = 'dbo'

The output reports are sorted by the permission category, owner, object, grantee, grantee, protection type category, protection type, action, and column continuous ID.

Permission

The execution permission is granted to the public role by default.

Example

A. Permission to list a table

The following example lists the table titles permissions.

Exec sp_helprotect 'titles'

B. List permissions of a user

The following example lists all the permissions of Judy in the current database.

Exec sp_helprotect null, 'judy'

C. List permissions granted by a specific user

The following example lists all permissions granted by user Judy in the current database, using null as a placeholder for missing parameters.

Exec sp_helprotect null, null, 'judy'

D. Only list statement Permissions

The following example lists all the statement permissions in the current database and uses NULL as a placeholder for missing parameters.

EXEC sp_helprotect NULL,'s'