Permissions

Hi, could someone help regarding the Create, Alter, Drop and Control permissions.

For example, say I want to create a table on a particular database. How do I determine if the user has the correct 'Create Table' permissions?

In MSDN, it continually refers to (for example)

"

To set Table object properties, users must have ALTER permission on the table.

To create a table, users must have CREATE TABLE permission on the parent database and ALTER permission in the schema.

To drop a table, users must have CONTROL permission on the table, or be a member of the db_ddladmin or db_owner fixed database roles.

To grant, deny, or revoke permission on the table to other users, users must have CONTROL permission on the table.

"

I have tried using the EnumObjectPermissions and EnumDatabasePermissions methods of the Database object in SMO, but having no luck. Anyideas?

Thanks

DAn

EnumDatabasePermissions should indeed give you the 'statement' permissions that users have on that database. You can also look at the various roles, and enumerate members to see whether a user is part of a role, such as db_ddladmin. Could you elaborate on what is not working for you?|||

Right I am trying to create an application, where by a authorised users (SQL Auth) can create a table within a particular database. However, I need to check to ensure that the 'user' as the permissions to perform the task and I am not sure how to achieve this.

I have tried to use the EnumDatabasePermissions, but every time I try no results are returned (no matter which SQL account I used, have even tested with 'sa'). I may be using the EnumDatabasePermissions incorrectly, but everythink i try seems to give the same responce.

I have tried the different call methods of EnumDatabasePermissions in the local debug window of VS2005 (i.e. EnumDatabasePermissions () or EnumDatabasePermissions ("sa") ) but still getting result {Length='0'}

I am using SQL Server 2000 and 2005

The code I have so far is

Public Function Create() As Boolean Implements Interfaces.IDefinition.Create

Dim booSuccess As Boolean = False

' Ensure table name and structure have been defined

If (_dtTableStructure.Rows.Count <> 0) And (_strTableName.Length <> 0) Then

'Connect to the local, default instance of SQL Server.

Dim SQLServer As Server

Dim Database As Smo.Database

Dim Table As Smo.Table

Dim Column As Smo.Column

Dim Index As Smo.Index

Try

' Set Server connection information

SQLServer = New Server(_ServerConnection)

' Connect to server

_ServerConnection.Connect()

' Set Database

Database = SQLServer.Databases(_strDatabaseName)

' Check if database Is Accessible

If Database.IsAccessible = True Then

' Check to ensure table does not already exist

If Database.Tables.Contains(_strTableName) = False Then

' Define table name within specified database

Table = New Smo.Table(Database, _strTableName)

' Check if index already exists

If Table.Indexes.Contains("PK_" & _strTableName) = False Then

' Drop index

Table.Indexes("PK_" & _strTableName).Drop()

End If

Index = New Index(Table, "PK_" & _strTableName)

Table.Indexes.Add(Index)

' Loop though defined table structure

For Each Row As DataRow In _dtTableStructure.Rows

' Column Name

Column = New Column(Table, Row.Item("ColumnName").ToString)

' DataType

Select Case CType(Row.Item("DataTypeID"), _Global.eDataTypes)

Case _Global.eDataTypes.bigint

Column.DataType = DataType.BigInt

Case _Global.eDataTypes.int

Column.DataType = DataType.Int

Case _Global.eDataTypes.smallint

Column.DataType = DataType.SmallInt

Case _Global.eDataTypes.tinyint

Column.DataType = DataType.TinyInt

Case _Global.eDataTypes.bit

Column.DataType = DataType.Bit

Case _Global.eDataTypes.decimal

Column.DataType = DataType.Decimal(0, 18)

Case _Global.eDataTypes.numeric

Column.DataType = DataType.Numeric(0, 18)

Case _Global.eDataTypes.money

Column.DataType = DataType.Money

Case _Global.eDataTypes.smallmoney

Column.DataType = DataType.SmallMoney

Case _Global.eDataTypes.float

Column.DataType = DataType.Float

Case _Global.eDataTypes.real

Column.DataType = DataType.Real

Case _Global.eDataTypes.datetime

Column.DataType = DataType.DateTime

Case _Global.eDataTypes.smalldatetime

Column.DataType = DataType.SmallDateTime

Case _Global.eDataTypes.char

Column.DataType = DataType.Char(Row.Item("Length").ToString)

Case _Global.eDataTypes.varchar

Column.DataType = DataType.VarChar(Row.Item("Length").ToString)

Case _Global.eDataTypes.text

Column.DataType = DataType.Text

Case _Global.eDataTypes.nchar

Column.DataType = DataType.NChar(Row.Item("Length").ToString)

Case _Global.eDataTypes.nvarchar

Column.DataType = DataType.NVarChar(Row.Item("Length").ToString)

Case _Global.eDataTypes.ntext

Column.DataType = DataType.NText

Case _Global.eDataTypes.binary

Column.DataType = DataType.Binary(Row.Item("Length").ToString)

Case _Global.eDataTypes.varbinary

Column.DataType = DataType.VarBinary(Row.Item("Length").ToString)

Case _Global.eDataTypes.image

Column.DataType = DataType.Image

Case _Global.eDataTypes.sql_variant

Column.DataType = DataType.Variant

Case _Global.eDataTypes.timestamp

Column.DataType = DataType.Timestamp

Case _Global.eDataTypes.uniqueidentifier

Column.DataType = DataType.UniqueIdentifier

End Select

' IsNullable

Column.Nullable = CType(Row.Item("AllowNulls"), Boolean)

Table.Columns.Add(Column)

' Primary Key

If CType(Row.Item("PrimaryKey"), Boolean) = True Then

' Add the primary key index

Index.IndexedColumns.Add(New IndexedColumn(Index, Column.Name))

Index.IsClustered = True

Index.IsUnique = True

Index.IndexKeyType = IndexKeyType.DriPrimaryKey

End If

Next

Try

' Attempt to create table

Table.Create()

' Success

booSuccess = True

Catch ex As Exception

' Set ExceptionMessage member variable

_objException = ex

End Try

Else

Dim ex As New Exception("test")

Throw ex

End If

End If

Catch ex As Exception

' Set ExceptionMessage member variable

_objException = ex

Finally

_ServerConnection.Disconnect()

End Try

End If

Return booSuccess

End Function