Re: permission assigned to the data base

Yosef
IF PERMISSIONS()&2=2
CREATE TABLE test_table (col1 INT)
ELSE
PRINT 'ERROR: The current user cannot create a table.'
--------
This example determines whether the current user can
grant the INSERT permission on the authors table to another user.

IF PERMISSIONS(OBJECT_ID('authors'))&0x80000=0x80000
PRINT 'INSERT on authors is grantable.'
ELSE
PRINT 'You may not GRANT INSERT permissions on authors.'

------http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_pa-pz_6f78.asp>
How can I retrieve a list of objects and permissions for a specified role?
------------------------------------------------------------------------------

SELECT HAS_DBACCESS ( 'database_name' )


In SQL Server 2005, you can use the Has_Perms_By_Name() function
(http://msdn2.microsoft.com/en-us/library/ms189802.aspx).

For example, I would like to list all stored procedures which a role has
execute permission for.

This is an example of usage:

SELECT o.SchemaAndName,
has_perms_by_name(o.SchemaAndName, 'OBJECT', 'EXECUTE')
FROM (SELECT name, SCHEMA_NAME(schema_id) AS [schema],
SCHEMA_NAME(schema_id)+'.'+name AS SchemaAndName
FROM sys.objects
WHERE type = 'P') AS o





"Yousef Alsayed" <josppy@xxxxxxxxxxxxxxxx> wrote in message
news:C00BC0DB-3476-4B2A-8C1F-655DCD5092C8@xxxxxxxxxxxxxxxx

Hi Erland,

please do you have a query can get this informaiton ?

Regards

Yousef Alsayed

"Erland Sommarskog" wrote:

Yousef Alsayed (josppy@xxxxxxxxxxxxxxxx) writes:
where the SQL 2005 store the permission assigned to the data base
example
create table , create view ,?.?

You find the database permissions in the catalog view
sys.database_permissions. Please see Books Online for details.

The functions fn_my_permissions and has_perms_by_object are also useful.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000:
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx




.

Relevant Pages

  • Re: Detailed User Access Rights
    ... Sysadmin role members have full SQL Server permissions and OS permissions ... > 2) Now that my customers can create their own views etc, ... GRANT SELECT ON MyTableFunction TO CustomerUser ...
    (microsoft.public.sqlserver.security)
  • ADP, Application Role, and objects
    ... The above link is to an atricle on how to implement SQL Server Application ... After you connect with your ADP, fire a bit of code to set the ... third party tools to view the data on the same database. ... Scenario 1 - If I explicitly grant permissions on that object to the user ...
    (microsoft.public.access.adp.sqlserver)
  • Re: field level security question
    ... Vyas, MVP ... listBox control that gets data from a query of the sql server table. ... > recent change in requirement) I see the option to limit permissions right ... > utility opens except their is just no data. ...
    (microsoft.public.sqlserver.security)
  • RE: SQL 2008 - remote connection to the SSIS 2008 - Access denied
    ... SQL Server configures the appropriate rights during the installation process, ... then add users/groups and assign the appropriate permissions. ... user who created the package apply to the writer role. ... I checked DCOM MsDts component and my user rights for the Remote ...
    (microsoft.public.sqlserver.dts)
  • Re: The user does not have permission to perform this action.
    ... exec sp_addlinkedserver 'remote_server_name' ... Execute permissions default to members of the sysadmin and setupadmin ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)