To obtain SQL Server database logon user permissions using SQL statements

Source: Internet
Author: User
Tags object log sql server sql string valid
server| Data | database | user RIGHTS | statement sp_helprotect
Returns a report that contains information about the user rights or statement permissions of an object in the current database.
Grammar
sp_helprotect [[@name =] ' object_statement '] [, [@username =] ' security_account '] [, [@grantorname =] ' gr Antor '] [, [@permissionarea =] ' type ']
Parameters
[@name =] ' Object_statement '

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

If Object_statement is a statement, you can:
CREATE databasecreate defaultcreate functioncreate procedurecreate rulecreate tablecreate VIEWBACKUP LOG
[@username =] ' Security_account '

is the name of the security account whose permissions are returned. The security_account data type is sysname and the default value is NULL, which returns all security accounts in the current database. Security_account must be a valid security account in the current database. When specifying microsoft®windows NT® users, specify the names that the Windows NT user can recognize in the database (added with sp_grantdbaccess).

[@grantorname =] ' Grantor '

is the name of the authorized security account. The grantor data type is sysname and the default value is NULL, which returns all information about 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 (added with sp_grantdbaccess).

[@permissionarea =] ' Type '

is a string indicating whether to display object permissions (String o), statement permissions (string s), or both (O s). Type is varchar (10) and the default value is O S. Type can be any combination of O and S, between O and S can or without commas or spaces.
Return code value
0 (Success) or 1 (failed)
The result set column name data type describes the name of the Ownersysname object owner. The name of the Objectsysname object. Granteesysname the name of the user to whom the permission is granted. Grantorsysname the name of the user to whom the specified authorized user is authorized. Protecttypechar (10) The name of the protection type:
Grantrevoke
Actionvarchar (20) The name of the permission:
Referencesselectinsertdeleteupdatecreate tablecreate databasecreate functioncreate RULECREATE VIEWCREATE Procedureexecutebackup databasecreate defaultbackup LOG
Type of columnsysname permission:
All = permissions apply to all current column of the object. The new = permission applies to any newer columns that can be modified on the object later (using the ALTER statement). The combination of all+new = All and New.
Comments
All of the parameters for this procedure are optional. If you perform sp_helprotect without parameters, all permissions that have been granted or denied in the current database are displayed.

If you specify a subset of the parameters, instead of specifying all the parameters, use named parameters to identify specific parameters, or use NULL as a placeholder. For example, to report all permissions for the grant dbo, perform:

EXEC sp_helprotect null, NULL, dbo

Or

EXEC sp_helprotect @grantorname = ' dbo '

The output reports are sorted by permission category, owner, object, grantee, grantor, protection type classification, protection type, action, and column serial ID.
Permissions
Execute permissions are granted to the public role by default.
Example A. List permissions for a table
The following example lists the permissions for table titles.

EXEC sp_helprotect ' titles '
B. Listing permissions for a user
The following example lists all the permissions that user Judy has in the current database.

EXEC sp_helprotect NULL, ' Judy '
C. List the permissions granted by a particular user
The following example lists all the permissions granted by user Judy in the current database, using NULL as a placeholder for missing parameters.

EXEC sp_helprotect null, NULL, ' Judy '
D. List only Statement permissions
The following example lists all statement permissions in the current database, using NULL as a placeholder for missing parameters.

EXEC sp_helprotect null, NULL, NULL, ' s '


Related Article

Alibaba Cloud 10 Year Anniversary

With You, We are Shaping a Digital World, 2009-2019

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.