Permission Denied Error: drop

Showing posts with label drop. Show all posts

Friday, March 9, 2012

permissions

Hi All,
how I can setup a role, so that they can only create, drop stored procedure,
views and functions. and can't create triggers, tables or modify schema.
Tim
grant create procedure, create view, create function to <login>
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"t" <t@.t.t> wrote in message news:ehIWCmpaEHA.4048@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> how I can setup a role, so that they can only create, drop stored
procedure,
> views and functions. and can't create triggers, tables or modify schema.
>
> Tim
>
>

permissions

Hi All,
how I can setup a role, so that they can only create, drop stored procedure,
views and functions. and can't create triggers, tables or modify schema.
Tim
Hi,
You can only assign CREATE PROC, CREATE FUNCTION, CREATE VIEW to a
USER/ROLE. The DROP
permissons for any of this objects is not grantable.
Thanks
Hari
MCDBA
"t" <t@.t.t> wrote in message news:eXYPwlpaEHA.384@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> how I can setup a role, so that they can only create, drop stored
procedure,
> views and functions. and can't create triggers, tables or modify schema.
>
> Tim
>
>

permissions

Hi All,
how I can setup a role, so that they can only create, drop stored procedure,
views and functions. and can't create triggers, tables or modify schema.
Timgrant create procedure, create view, create function to <login>
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"t" <t@.t.t> wrote in message news:ehIWCmpaEHA.4048@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> how I can setup a role, so that they can only create, drop stored
procedure,
> views and functions. and can't create triggers, tables or modify schema.
>
> Tim
>
>

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

permissions

Hi All,
how I can setup a role, so that they can only create, drop stored procedure,
views and functions. and can't create triggers, tables or modify schema.
Timgrant create procedure, create view, create function to <login>
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"t" <t@.t.t> wrote in message news:ehIWCmpaEHA.4048@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> how I can setup a role, so that they can only create, drop stored
procedure,
> views and functions. and can't create triggers, tables or modify schema.
>
> Tim
>
>

Wednesday, March 7, 2012

Permission to execute a job

I have a stored proc that refreshes data and then does a table swap.
As part of the table swap I need to drop and create a table. This requires
basically dbo permissions.
So I create a job to run my proc and the job runs under the sa accout.
But I need an account that is not sa or dbo to have the the ability to run
this job as needed rather than just having it run when scheduled.
Is it possible to grant permissions to a non-sa/dbo account so that it can
call this job?
IOW, I don't want my non-sa/dbo account to have any special dbo-like
permissions except the ability to execute a table swap within the context of
my stored proc.
How can I best accomplish this?Create a table for your sp to add a row to when it wants to run the job.
Have a scheduled job poll that table and when it sees a new row it starts
your job.
"Dave" wrote:

> I have a stored proc that refreshes data and then does a table swap.
> As part of the table swap I need to drop and create a table. This require
s
> basically dbo permissions.
> So I create a job to run my proc and the job runs under the sa accout.
> But I need an account that is not sa or dbo to have the the ability to run
> this job as needed rather than just having it run when scheduled.
> Is it possible to grant permissions to a non-sa/dbo account so that it can
> call this job?
> IOW, I don't want my non-sa/dbo account to have any special dbo-like
> permissions except the ability to execute a table swap within the context
of
> my stored proc.
> How can I best accomplish this?
>
>

permission to create dbo.proc but not drop table

Hi group,
is there a way to create a role, assign permission to it so that it can
create procedures with dbo as owner, and not able to drop tables?
QuentinAlthough you can't create a user role for this, you can add a user to the
db_ddladmin fixed database role and then DENY those statement permissions
you don't want the user to have For example:
EXEC sp_addrolemember 'db_ddladmin' ,'MyUser'
DENY CREATE TABLE TO MyUser1
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Quentin Ran" <ab@.who.com> wrote in message
news:OxJQ8o4MEHA.1312@.TK2MSFTNGP12.phx.gbl...
> Hi group,
> is there a way to create a role, assign permission to it so that it can
> create procedures with dbo as owner, and not able to drop tables?
> Quentin
>|||Dan,
thanks for the response. I have been there -- you can still drop tables.
Quentin
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:#7xngl9MEHA.2736@.TK2MSFTNGP11.phx.gbl...
> Although you can't create a user role for this, you can add a user to the
> db_ddladmin fixed database role and then DENY those statement permissions
> you don't want the user to have For example:
> EXEC sp_addrolemember 'db_ddladmin' ,'MyUser'
> DENY CREATE TABLE TO MyUser1
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Quentin Ran" <ab@.who.com> wrote in message
> news:OxJQ8o4MEHA.1312@.TK2MSFTNGP12.phx.gbl...
> > Hi group,
> >
> > is there a way to create a role, assign permission to it so that it can
> > create procedures with dbo as owner, and not able to drop tables?
> >
> > Quentin
> >
> >
>|||> thanks for the response. I have been there -- you can still drop tables.
Sorry about that. I tested my suggestion with CREATE TABLE but not DROP
TABLE. I suggest you send this to sqlwish@.microsoft.com to present your
case for including this functionality in a future SQL Server version.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Quentin Ran" <ab@.who.com> wrote in message
news:uVpgV1DNEHA.2388@.TK2MSFTNGP09.phx.gbl...
> Dan,
> thanks for the response. I have been there -- you can still drop tables.
> Quentin
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:#7xngl9MEHA.2736@.TK2MSFTNGP11.phx.gbl...
> > Although you can't create a user role for this, you can add a user to
the
> > db_ddladmin fixed database role and then DENY those statement
permissions
> > you don't want the user to have For example:
> >
> > EXEC sp_addrolemember 'db_ddladmin' ,'MyUser'
> > DENY CREATE TABLE TO MyUser1
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Quentin Ran" <ab@.who.com> wrote in message
> > news:OxJQ8o4MEHA.1312@.TK2MSFTNGP12.phx.gbl...
> > > Hi group,
> > >
> > > is there a way to create a role, assign permission to it so that it
can
> > > create procedures with dbo as owner, and not able to drop tables?
> > >
> > > Quentin
> > >
> > >
> >
> >
>|||> I suggest you send this to sqlwish@.microsoft.com to present your
> case for including this functionality in a future SQL Server version.
Thanks Dan. Did that.
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:OexgQ8DNEHA.3380@.TK2MSFTNGP11.phx.gbl...
> > thanks for the response. I have been there -- you can still drop
tables.
> Sorry about that. I tested my suggestion with CREATE TABLE but not DROP
> TABLE. I suggest you send this to sqlwish@.microsoft.com to present your
> case for including this functionality in a future SQL Server version.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Quentin Ran" <ab@.who.com> wrote in message
> news:uVpgV1DNEHA.2388@.TK2MSFTNGP09.phx.gbl...
> > Dan,
> >
> > thanks for the response. I have been there -- you can still drop
tables.
> >
> > Quentin
> >
> > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> > news:#7xngl9MEHA.2736@.TK2MSFTNGP11.phx.gbl...
> > > Although you can't create a user role for this, you can add a user to
> the
> > > db_ddladmin fixed database role and then DENY those statement
> permissions
> > > you don't want the user to have For example:
> > >
> > > EXEC sp_addrolemember 'db_ddladmin' ,'MyUser'
> > > DENY CREATE TABLE TO MyUser1
> > >
> > > --
> > > Hope this helps.
> > >
> > > Dan Guzman
> > > SQL Server MVP
> > >
> > > "Quentin Ran" <ab@.who.com> wrote in message
> > > news:OxJQ8o4MEHA.1312@.TK2MSFTNGP12.phx.gbl...
> > > > Hi group,
> > > >
> > > > is there a way to create a role, assign permission to it so that it
> can
> > > > create procedures with dbo as owner, and not able to drop tables?
> > > >
> > > > Quentin
> > > >
> > > >
> > >
> > >
> >
> >
>

Permission Denied Error

Friday, March 9, 2012

permissions

permissions

permissions

Permissions

permissions

Wednesday, March 7, 2012

Permission to execute a job

permission to create dbo.proc but not drop table

permission to create dbo.proc but not drop table

permission to create dbo.proc but not drop table

Followers

Blog Archive

About Me