Permissions Question For SQL Login Account

I have a SQL login account defined with DBO permissions on a particular
database. When this login attempts to run the sp_updatestats stored
procedure, the following error occurs: "User does not have permission to
perform this action." According to BOL, the DBO has permissions to execute
this stored procedure. Any help would be appreciated.MACason wrote:
> I have a SQL login account defined with DBO permissions on a
> particular database. When this login attempts to run the
> sp_updatestats stored procedure, the following error occurs: "User
> does not have permission to perform this action." According to BOL,
> the DBO has permissions to execute this stored procedure. Any help
> would be appreciated.
Run this code (from sp_updatestats) and see what it returns. If it fails
then the user is not a sysadmin and not the dbo in the database. There
is only one dbo per database (they cannot be aliased as dbo I don't
think):
DECLARE @.dbsid varbinary(85)
SELECT @.dbsid = sid
FROM master.dbo.sysdatabases
WHERE name = db_name()
select @.dbsid
select suser_sid()
/*Check the user sysadmin*/
IF NOT is_srvrolemember('sysadmin') = 1 AND suser_sid() <> @.dbsid
BEGIN
RAISERROR(15247,-1,-1)
RETURN (1)
END
David Gugick
Imceda Software
www.imceda.com|||The Books Online states:
<Excerpt href="http://links.10026.com/?link=tsqlref.chm::/ts_sp_ua-uz_14kz.htm">
Only the DBO and members of the sysadmin fixed server role can execute this
procedure.
</Excerpt>
Note that db_owner role members are not *the* 'dbo'. The database owner is
the login that owns the database.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"MACason" <MACason@.discussions.microsoft.com> wrote in message
news:47413145-85BC-4F7C-AC60-245E011F324D@.microsoft.com...
>I have a SQL login account defined with DBO permissions on a particular
> database. When this login attempts to run the sp_updatestats stored
> procedure, the following error occurs: "User does not have permission
> to
> perform this action." According to BOL, the DBO has permissions to execute
> this stored procedure. Any help would be appreciated.

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