How to create objects under dbo without DDL admin rights??

Does someone have any suggestions on what rights to grant a user to allow
them to only create stored procedures, functions under dbo without granting
DDL admin?Hi,
Open Database properties and the under permissions you can set for user to
create and alter objects.
Danijel Novak
"John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
news:344873E1-75CC-441F-A9FF-B63ECF38E7A9@.microsoft.com...
> Does someone have any suggestions on what rights to grant a user to allow
> them to only create stored procedures, functions under dbo without
> granting
> DDL admin?|||That I know, but I want a user to be able to create a new procedure, but
create it as dbo.procedure. As far as I know, you cannot do this without DDL
Admin rights.
"Danijel Novak" wrote:

> Hi,
> Open Database properties and the under permissions you can set for user to
> create and alter objects.
> --
> Danijel Novak
>
> "John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
> news:344873E1-75CC-441F-A9FF-B63ECF38E7A9@.microsoft.com...
>
>|||In SQL 2000 and earlier versions, the user must be either:
- a db_ddladmin role member
- a db_owner role member
- the database owner
- a sysadmin role member
Of course, membership in these role provides considerably more permissions
as well.
Note that the ability to create dbo-owned procs and functions essentially
allows users to retrieve and manipulate data in all dbo-owned tables. This
is one reason why one typically allows only DBAs to create dbo-owned
objects.
Hope this helps.
Dan Guzman
SQL Server MVP
"John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
news:344873E1-75CC-441F-A9FF-B63ECF38E7A9@.microsoft.com...
> Does someone have any suggestions on what rights to grant a user to allow
> them to only create stored procedures, functions under dbo without
> granting
> DDL admin?|||Hi,
true said by Dan , that's why ! we only do it with only
- a db_ddladmin role member
- a db_owner role member
- the database owner
- a sysadmin role member , so in shore please don't permit it to not dbo
user.
:-)
Regards
--
Andy Davis
Activecrypt Team
---
SQL Server Encryption Software
http://www.activecrypt.com
"Dan Guzman" wrote:

> In SQL 2000 and earlier versions, the user must be either:
> - a db_ddladmin role member
> - a db_owner role member
> - the database owner
> - a sysadmin role member
> Of course, membership in these role provides considerably more permissions
> as well.
> Note that the ability to create dbo-owned procs and functions essentially
> allows users to retrieve and manipulate data in all dbo-owned tables. Thi
s
> is one reason why one typically allows only DBAs to create dbo-owned
> objects.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
> news:344873E1-75CC-441F-A9FF-B63ECF38E7A9@.microsoft.com...
>
>

How to create an object without being the owner of the schema

Hi

I have a schema called Accounts owned by fred

User bob has create procedure permission as follows:

grant create procedure to bob

bob would like to create a procedure in schema Accounts.

When he issues create proc Accounts.sp_proc.... it fails with:

Msg 2760, Level 16, State 1, Procedure sp_proc, Line 3
The specified schema name "Accounts" either does not exist or you do not have permission to use it.

What permission do I need to grant bob in order to allow this?

Thanks

The answer is in Books Online; look for the Permissions section in the CREATE PROCEDURE article:

http://msdn2.microsoft.com/en-us/library/ms187926(SQL.90).aspx

Thanks
Laurentiu

|||

Thanks Laurentiu, I couldn't see the wood for the trees.

In case anyone else is interested I needed to run:

GRANT ALTER ON SCHEMA::Accounts TO bob

sql

How to create a login/user & grant rights?

I'm trying to create new login/user account in C#. I'm pretty close to getting it working -- login/user accounts get created, but I'm getting hung up on granting the permissions for the User on the database.

The error that is thrown is:
Microsoft.SqlServer.Management.Smo.FailedOperationException: Grant failed for User 'RTOUser'. > Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. > System.Data.SqlClient.SqlException: Incorrect syntax near 'CONNECT...'.

With the SQL server profiler, I can see the grant command that was used:
GRANT CONNECT, DELETE, EXECUTE, INSERT, SELECT ON USER::[RTOUser] TO [test14]

RTOUser is just a generic testing account I'm using that basically has admin rights on the database. It also has with grant rights for the aforementioned rights being granted to the test user, test14.

How does one post formatted code on here? a [code] bracket doesn't work nor do I see anything in the formatting bar above for it. Below is the code I'm using:

public override Int32 CreateEmployee( EmployeeData emp )
{
Int32 lastid = 0;
SqlParameter[] insert_parms = {
new SqlParameter("@.EmpUserName", SqlDbType.VarChar, STD_VARCHAR),
new SqlParameter("@.EmpFullName", SqlDbType.VarChar, STD_VARCHAR),
new SqlParameter("@.EmpDescription", SqlDbType.VarChar, STD_VARCHAR)
};

insert_parms[0].Value = emp.m_UserName;
insert_parms[1].Value = emp.m_FullName;
insert_parms[2].Value = emp.m_Description;

try
{
Server svr = SqlHelper.GetServerConnection( this.ConnectionString );
Login lg = new Login( svr, emp.m_UserName );

if ( !svr.Logins.Contains( emp.m_UserName ) )
{
lg.LoginType = LoginType.SqlLogin;
lg.PasswordPolicyEnforced = false; //really should be true
lg.DefaultDatabase = DBNAME;
lg.Create( emp.m_Password );

Database db = svr.Databases[DBNAME];
User u = new User( db, this.UserName );
u.Login = emp.m_UserName;
ObjectPermissionSet perms = new ObjectPermissionSet();

//todo: revise permissions
perms.Connect = true;
perms.Select = true;
perms.Insert = true;
perms.Delete = true;
perms.Execute = true;
u.Grant( perms, emp.m_UserName );
u.Create();

try
{
int.TryParse( SqlHelper.ExecuteScalar( this.DBSqlConnection, CommandType.Text, SQL_INSERT_EMPLOYEE, insert_parms ).ToString(), out lastid );
}
catch(SqlException)
{
throw;
}
}
}
catch (SmoException ex)
{
Console.WriteLine( ex );
}

return lastid;
}Additionally,

the User.Login Intellisense in VS2K5 w/SP1 says "Gets the login that is

associated with the database user". This is wrong since it can also

SET it.|||Bah, figured it out. After thinking what the heck SMO was trying to do with that grant command, I realized how it was doing it wrong. The database needs to grant the rights to the user, not a user to another user.

Functional code snippet:

Database db = svr.Databases[DBNAME];
User u = new User( db, emp.m_UserName );
u.Login = emp.m_UserName;
u.Create();

//todo: revise permissions once user groups functional
DatabasePermissionSet perms = new DatabasePermissionSet();
perms.Select = true;
perms.Insert = true;
perms.Delete = true;
perms.Update = true;
perms.Execute = true;

db.Grant( perms, emp.m_UserName );