Granting User Privileges??

I have setup an SQL MSDE instance on my PC and I currently finished working
on the BE in an access 2003 project. I believe it is now time to define
roles, users and grant each one its privileges, but how do I do that?
I know about GRANT, REVOKE etc, but
1. Where do I type these?
2. Is all this information encrypted?
3. When installing the MSDE I set a password what is this all about? Will
it have any impact on user level security?
4. Is there a visual tool to handle user level security, such as in an
Access database?
5. As I indend to create a distributed database application is these
security scheme in accordance to that?
6 and final. How can I let the superuser (aka the superior user of the
client) grant privileges to other users, after granting him the option to
grant? eg form?
Some answers inline
"Dimitrios Tanis" <jtanis@.mycosmos.gr> wrote in message news:O%23XXjhBnFHA.3608@.TK2MSFTNGP15.phx.gbl...
>I have setup an SQL MSDE instance on my PC and I currently finished working on the BE in an access 2003 project. I
>believe it is now time to define roles, users and grant each one its privileges, but how do I do that?
> I know about GRANT, REVOKE etc, but
> 1. Where do I type these?
In a script, run with osql.exe against the database is one way.
Query Analyser from a developer pc or pc with sql server installed is another

> 2. Is all this information encrypted?
Not unless it is embedded in your application, and only run locally or through an encrypted connection.
Even if it was scripting the database will get this configuration type of information back. The encyption in
SQL Server 2000 / MSDE is for view, triggers and stored procedures - i.e. code, and is crackable anyway.

> 3. When installing the MSDE I set a password what is this all about? Will it have any impact on user level security?
Depends on what authentication mode you use - with windows authentication, the only password you
have to worry about is the sa one on the install, and this is only a problem if someone at some stage
changes the security to SQL/Mixed.

> 4. Is there a visual tool to handle user level security, such as in an Access database?
If you map windows groups to the SQL database roles, then your user management is done under windows itself.
The beta for SQL 2005 Express includes a GUI tool, and there is a web based one for 2000.
http://www.microsoft.com/downloads/d...displaylang=en
http://www.microsoft.com/downloads/d...DisplayLang=en
Apart from them there is osql.exe for the masochistic, Enterprise Manager for the licensed, or your application
doing sql calls behind the scenes.

> 5. As I indend to create a distributed database application is these security scheme in accordance to that?
Well you are going to have to enable the network libraries, which immediatly gives you an order of magnitude
more security worries. For example a breach in your install of MSDE can grant full access to the PC (xp_cmdshell).
Integrated security is Microsoft's recommendation, but you still need to think about changing network ports and
firewalling.

> 6 and final. How can I let the superuser (aka the superior user of the client) grant privileges to other users, after
> granting him the option to grant? eg form?
see 4)
also suggest reading security related info from
http://msdn.microsoft.com/library/de...stsql_84xl.asp
Regards
AJ

Granting UPDATE for only certain columns in a table

I have tried using the SQL statement shown below to grant UPDATE permissions for a single column in a single table to a user with db_datareader privileges.

grant update (col_1) on trs.dbo.table_1 to calc

When I then run a SQL script that has an UPDATE for col_1 on trs.dbo.table_1, I get an error message

Msg 230, Level 14, State 1, Line 2100

UPDATE permission denied on column 'col_2' of object 'table_1', database 'TRS', schema 'dbo'.

Why is the error message referring to "col_2" when my SQL statement is trying to update "col_1"?

When I performed the "grant" I did it with an account that has db_owner, db_securityadmin, and db_ddladmin privileges.

This worked in SQL Server 2000. What must I do to get it to work in SQL Server 2005?

Sorry Dan - can you show us the update query?|||

You are going to have to dig deeper. The only scenario that I can think of where an update to another column causes an update to another column causing issues like this is with a trigger with dynamic SQL (a real no-no in almost all cases, but it could exist). The fact that you have appended database names to the ddl makes me curious as to how that *might* cause issue, but I don't even see how anything cross-database could be an issue either.

Here is a script that shows what I am meaning:

create table test
(
column1 int,
column2 int
)
go
create user fred without login
go
execute as user = 'fred'
go
update test
set column1 = 1
/*
Msg 229, Level 14, State 5, Line 1
The UPDATE permission was denied on the object 'test', database 'tempdb', schema 'dbo'.
*/
go
revert
go
grant update (column1) on test to fred
go
execute as user = 'fred'
go
update test
set column1 = 1
/*
(0 row(s) affected)
*/
update test
set column2 = 1
/*
Msg 230, Level 14, State 1, Line 1
The UPDATE permission was denied on the column 'column2' of the object 'test', database 'tempdb', schema 'dbo'.
*/
go
revert
go
create trigger test$updateColumn1
on test
after update
as
begin
exec('
update test
set column2 = 2')
end
go
execute as user = 'fred'
go
update test
set column1 = 1
/*
Msg 230, Level 14, State 1, Line 2
The UPDATE permission was denied on the column 'column2' of the object 'test', database 'tempdb', schema 'dbo'.
*/

If you could post a full example like this showing your issue I think that you might find your error, or certainly one of us can help you out.

|||

Thanks for your support.

Here is a small bit of code that is able to produce the problem. It seems to be associated with having a JOIN in the UPDATE statement. An UPDATE without the JOIN works just fine.

Using a DB_OWNER account perform the following table creation, insert, and grant commands:

create table trs.dbo.people
(
name varchar(10),
sex varchar(10),
age smallint
)

insert into people values ('tom', 'male', 10)
insert into people values ('jane', 'female', 18)
insert into people values ('sue', 'female', 22)

create table trs.dbo.grads
(
name varchar(10),
grad_yr varchar(4)
)

insert into grads values ('jane', '2006')
insert into grads values ('sue', '2002')

grant update (age) on trs.dbo.people to calc

Then, from the db_datareader account, calc, run the following UPDATE statements, one at a time.

update p
set age = 40
from trs.dbo.people p
where (age is not null)
;

update p
set age = 35
from trs.dbo.people p
inner join trs.dbo.grads g
on p.name = g.name
where (age is not null)
;

The first one works. The second one fails with error message

Msg 230, Level 14, State 1, Line 1

UPDATE permission denied on column 'name' of object 'people', database 'TRS', schema 'dbo'.

Does this help?

|||Very nice. I have no answer as to why this is, but I will check around and let you know.|||Thanks!|||Do you have select permission on the other columns?

I know it's complaining that you don't have UPDATE permission... but it's complaining about your access to another column that's involved in the join.

Rob|||

Rob,

If I enter SELECT statements against the two tables, while connected as "CALC", all the rows from each table are returned.

select * from grads;

select * from people;

I have also tried using the DB_OWNER connection to explicity GRANT SELECT access to those tables, and still get the same error message when I try to update the AGE column, as in the example above, while connected as "CALC".

grant select on trs.dbo.people to calc
grant select (name) on trs.dbo.people to calc
grant select on trs.dbo.grads to calc

Dan

|||Ok, so that idea wasn't right. ;)

It's somehow related to the fact that you're joining the table you're updating to another table, and the engine thinks that it needs to be able to update that column too. It doesn't of course, but the fact that the column is used in the query must be confusing it somehow.

I assume this works just fine if you grant update access to the name column too?

One workaround might be to wrap it up in a table expression, but if that works, it'll just be down to luck.

Rob|||

The same code runs just fine with SQL Server 2000. I only ran into a problem when I tried moving it over to SQL Server 2005.

If I grant UPDATE access on the NAME column, as identified in the Error message, then the code runs.

But I hate to do that, since CALC is supposed to be a relatively "unprivileged" user, with db_datareader general privileges only, and CREATE TABLE privileges -- and in my actual application, these other columns are the PRIMARY KEY columns in the tables.

Adding a new column to the table, a column to which CALC has UPDATE permission, was a way of avoiding creating an entirely new table for CALC to own, with 500,000 rows, and a 20-byte primary key that incorporates 5 columns. Had I created such a new table my processing queries would have to JOIN this table to the 500,000 row counterpart. Instead of such a JOIN I added a new column as a simpler, faster solution.

Dan

|||Hmm... I'll have to do some more hunting. I'd like to say "try using a cte or view", or something like that (based on the fact that you might be able to update a view without it thinking it needs to be able to update the joining column), but I actually don't know if that will help. I'll have to recreate the situation locally and try some things.

But hopefully some of the other guys will already know the answer to this.

Rob|||

This seems like such a "natural" thing to have to do, in most any application that requires user permissions to only certain columns in a table.

Consider a table that connects salary to social security number. If you want to allow someone to edit SALARY, must you also allow them to edit SOCIAL SECURITY NUMBER?

What if you are running a medical office and have some sort of PATIENT NUMBER in the medical records. If you want to edit information on their account, such as their ZIP CODE, must you also allow them to edit the PATIENT NUMBER?

Is there maybe a greater need in SQL Server 2005 to create a new ROLE for every different set of permissions that one might need in the database, rather than use GRANT UPDATE to customize access to different users?

I'm not now at my office where I could try it, but I'm wondering if some SYSTEM table stores UPDATE permissions, and if a user has NOT received GRANT or DENY UPDATE permissions on a column in a table, maybe an INNER JOIN is being used, rather than an OUTER JOIN on the permissions table. If that is true, I suppose I can DENY UPDATE on the NAME column in the example, and the query should work, because it finds an entry in the permissions table for the NAME column, even though that entry is "DENY." So tomorrow at the office I'll try DENY UPDATE (name) on TRS.DBO.PEOPLE to CALC. (I'll have to check the syntax on the DENY statement. I think I noticed one in the past few days.)

Dan

|||

This is almost clearly a bug that I posted here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=257897

A (not terribly satisfying) workaround is to move the join to a subquery:

update p set age = 35
from trs.dbo.people p
where p.name in (select p2.name
from trs.dbo.people p2
inner join trs.dbo.grads g
on p.name = g.name
where (age is not null))

Here is the simple repro with users I posted:

create database trs
go
use trs
go
create table trs.dbo.people
(
name varchar(10),
sex varchar(10),
age smallint
)

insert into people values ('tom', 'male', 10)
insert into people values ('jane', 'female', 18)
insert into people values ('sue', 'female', 22)

create table trs.dbo.grads
(
name varchar(10),
grad_yr varchar(4)
)

insert into grads values ('jane', '2006')
insert into grads values ('sue', '2002')
go
create user calc without login
sp_addrolemember 'db_datareader','calc'
go
grant update (age) on trs.dbo.people to calc
go

--Then, from the db_datareader account, calc, run the following UPDATE
statements, one at a time.
execute as user = 'calc'
go

update p
set age = 40
from trs.dbo.people p
where (age is not null)
;

update p
set age = 35
from trs.dbo.people p
inner join trs.dbo.grads g
on p.name = g.name
where (age is not null)
;

/*
Msg 230, Level 14, State 1, Line 1
The UPDATE permission was denied on the column 'name' of the object
'people', database 'trs', schema 'dbo'.
*/
--

|||

Louis,

Thanks for posting that to the MS "feedback" site.

I tried my "DENY UPDATE" idea, but it didn't fix anything: the same error message was obtained for the same UPDATE statement.

There aren't many places in my code where I am performing updates on columns where the GRANT UPDATE permission is limited to certain columns -- maybe a few dozen. I'll just make the code edits corresponding to your suggestion, and maybe un-do them if/when a patch occurs.

Thanks again.

Dan

|||

Louis,

I reworked my code for SQL Server 2005, using the technique you suggested (or something quite similar):

update p set age = 35
from trs.dbo.people p
where p.name in (select p2.name
from trs.dbo.people p2
inner join trs.dbo.grads g
on p.name = g.name
where (age is not null))

In all but a single instance, this solution worked just fine.

In the remaining instance, the value that I need for the SET clause is from the JOINed table. Were we using the example I supplied (rather than my actual code), this could appear as

update p
set age = cast(g.grad_yr as int) + 18 - 2006
from trs.dbo.people p
inner join trs.dbo.grads g
on p.name = g.name
where (age is not null)

I didn't see any easy way around this, other than to create a CURSOR on "select distinct GRAD_YR from GRADS" and using a LOOP over the CURSOR values, and having code like

update p
set age = @.grad_yr + 18 - 2006
from trs.dbo.people p
where (age is not null) and p.name in (select distinct name from trs.dbo.grads where grad_yr = @.grad_yr)

I am thankful that the number of values for my CURSOR is less than 10, in my actual application.

If you can think of a better alternative, I would be happy to learn of it.

Thanks.

Dan

Granting right to all the user sp's

Hello,
I would like to ease granting the right to execute all user sp's by normal u
sers.
By default, AFIK you have to click the EXECUTE option of each and every sp.
I head about a procedure, which could automate this.
Anyone knows this kind of procedure or how to to this?
Thank You
JoachimHi,
use pubs
go
select 'grant execute on ' +name +' to user_name' from sysobjects where
type='p'
Replace the database name and user_name based on your requirement.
After excuting the script you will get a bunch of grant statement in your
result pane, just cut and paste the entire contents and execute it again.
Thanks
Hari
MCDBA
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:404C5069.C80FF06E@.freenet.de...
> Hello,
> I would like to ease granting the right to execute all user sp's by normal
users.
> By default, AFIK you have to click the EXECUTE option of each and every
sp.
> I head about a procedure, which could automate this.
> Anyone knows this kind of procedure or how to to this?
> Thank You
> Joachim|||You can use a procedure like this
use master
go
create procedure sp_grantexec(@.user sysname,@.debug int = 0)
as
set nocount on
declare @.ret int
declare @.sql nvarchar(4000)
declare @.db sysname ; set @.db = DB_NAME()
declare @.u sysname ; set @.u = QUOTENAME(@.user)
-- check user exists
if not exists(select * from sysusers where name = @.user)
begin
raiserror('User %s is not a valid user in this database',16,1,@.user)
return -1
end
set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM
INFORMATION_SCHEMA.ROUTINES ' +
'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'
'IsMSShipped'') =
0'
if @.debug = 1 print @.sql
exec @.ret = master.dbo.xp_execresultset @.sql,@.db
If @.ret <> 0
begin
raiserror('Error executing command %s',16,1,@.sql)
return -2
end
go
Then you can run it like below to grant user foo exec permissions on all
user stored procedures in the pubs database
use pubs
go
exec sp_grantexec 'foo'
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:404C5069.C80FF06E@.freenet.de...
> Hello,
> I would like to ease granting the right to execute all user sp's by normal
users.
> By default, AFIK you have to click the EXECUTE option of each and every
sp.
> I head about a procedure, which could automate this.
> Anyone knows this kind of procedure or how to to this?
> Thank You
> Joachim

granting privilages similar to existing user

I need to grant privilages to a user that are like the privilages of an
existing user. How can I do this?
chuck t.The easiest way would be to write a little SQL-DMO script. Create a user
object and then execute the ListObjectPermissions method. Alternatively,
you can use the Script method and replace the old user with the new user.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Chuck" <Chuck@.discussions.microsoft.com> wrote in message
news:335EB4BA-1BCF-4151-9446-5D4BCA569657@.microsoft.com...
>I need to grant privilages to a user that are like the privilages of an
> existing user. How can I do this?
> --
> chuck t.|||Hi,
please check this if it help you to get list :
http://www.sql-server-performance.c...?TOPIC_ID=10504
:-)
Regards
--
Andy Davis
Activecrypt Team
---
SQL Server Encryption Software
http://www.activecrypt.com
"Chuck" wrote:

> I need to grant privilages to a user that are like the privilages of an
> existing user. How can I do this?
> --
> chuck t.

Granting permissions to xp_regread

Is it possible to grant execute permissions to xp_regread to a user who isn'
t
a member of the sysadmins role?Public has execute permissions on xp_regread so why would
you need to? But yes...you can grant execute.
-Sue
On Tue, 18 Oct 2005 20:28:02 -0700, "David"
<David@.discussions.microsoft.com> wrote:

>Is it possible to grant execute permissions to xp_regread to a user who isn
't
>a member of the sysadmins role?

Granting permissions to stored procedures

I am using the following code to grant user access to the stored procedures in my database. However, it does not appear to be working because I am getting an access denied message when running the application as the user.

Here is the code I am using:

Code Snippet

' Grant privileges

Dim ExecutePrivilege As New ObjectPermissionSet

ExecutePrivilege.Execute = True

' Grant privileges to all non-system stored procs

' The following line improves performance

SmoServer.SetDefaultInitFields(GetType(StoredProcedure), "IsSystemObject")

For Each sp As StoredProcedure In db.StoredProcedures

If Not sp.IsSystemObject Then

sp.Grant(ExecutePrivilege, loginName)

End If

Next

Is there something else I need to do, like a Save or Refresh or something? (I've stepped through the code and it *is* executing for each of my stored procedures. It just does not appear to actually have updated the priviledge.)

Any tips or ideas would be appreciated.

Thanks!

Security related commands are always executed directly, unless you change the setting from the context to only script the commands like the following statement does:

svr.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql;

The reflected sources show that a property is set for this which is called

so.ForDirectExecution = true;

Did you have a look in the profiler to see if the commands are arriving at the server and are eventually bounced back due to errors occuring during applying the script ?

Jens K. Suessmeyer

http://www.sqlserver2005.de|||

I skipped the profiler and went right to the database. I can view permissions, and they are actually set correctly. So the code is executing. (Should have thought of that before I posted!<G>)

The problem is that when I try to access any of the stored procs (or the tables), I get a "permission was denied on the object" message. So something else is obviously wrong.

This particular code happens to be in VB6, accessing SQLServer Express. When the *same* code accesses a SQL Server 2000 database, it runs without this error.

Any idea what could be wrong here? Or at this point do I need to move the question elsewhere since it does not appear to be an SMO problem.

sql

Granting permissions

I have a production server with a few databases. Each database has about
100-150 tables, and 200 or so Stored Procs.
I have a new user john that gets created on each database. I need to give
him the ability to select, insert, update and delete from any user table, and
to be able to execute any Stored Procedure.
If I grant this new user membership to db_datareader, he gets select from
any user table.
If I grant this new user membership to db_datawriter, he gets insert,
update, delete to any user table.
So far, so good. Now the problem -
What role can I grant that would allow this user to be able to run any
stored procedure? Note that my manager will not let me grant him db_owner, as
this gives him too much rights.
And what every I do to give him the above rights, he can't be allowed to
create tables, create views or create any stored procedures
I could do this manually through the GUI, but with over 200 stored procs per
table, this is too time consuming. I could script, but this is also something
labor intensive, am looking for an easier way.
Thank you in advance.
Sam
Think I found the solution. For each table, use the:
DENY CREATE VIEW to john
DENY CREATE TABLE to john
DENY CREATE SP to john
Still not sure how to give a user rights to execute all Stored Procedures,
though.
Sam
"Sam" wrote:

> I have a production server with a few databases. Each database has about
> 100-150 tables, and 200 or so Stored Procs.
> I have a new user john that gets created on each database. I need to give
> him the ability to select, insert, update and delete from any user table, and
> to be able to execute any Stored Procedure.
> If I grant this new user membership to db_datareader, he gets select from
> any user table.
> If I grant this new user membership to db_datawriter, he gets insert,
> update, delete to any user table.
> So far, so good. Now the problem -
> What role can I grant that would allow this user to be able to run any
> stored procedure? Note that my manager will not let me grant him db_owner, as
> this gives him too much rights.
> And what every I do to give him the above rights, he can't be allowed to
> create tables, create views or create any stored procedures
> I could do this manually through the GUI, but with over 200 stored procs per
> table, this is too time consuming. I could script, but this is also something
> labor intensive, am looking for an easier way.
> Thank you in advance.
> Sam
|||Create a new role in each database.
Grant the appropriate permissions to this role. (I know it's time
consuming, but it's a one time deal).
Then add your new users to this role in each db.
If you check around, there have been several scripts already created that
will grant permissions. You just need to tweak them a bit. My guess is
that in an hour worth of your time, you will have the script completed.
This newsgroup has had several posted to it within the last two weeks.
Rick Sawtell
MCT, MCSD, MCDBA
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:E7839D71-7EC7-47A0-96DF-4A62DAEED1D0@.microsoft.com...
> I have a production server with a few databases. Each database has about
> 100-150 tables, and 200 or so Stored Procs.
> I have a new user john that gets created on each database. I need to give
> him the ability to select, insert, update and delete from any user table,
and
> to be able to execute any Stored Procedure.
> If I grant this new user membership to db_datareader, he gets select from
> any user table.
> If I grant this new user membership to db_datawriter, he gets insert,
> update, delete to any user table.
> So far, so good. Now the problem -
> What role can I grant that would allow this user to be able to run any
> stored procedure? Note that my manager will not let me grant him db_owner,
as
> this gives him too much rights.
> And what every I do to give him the above rights, he can't be allowed to
> create tables, create views or create any stored procedures
> I could do this manually through the GUI, but with over 200 stored procs
per
> table, this is too time consuming. I could script, but this is also
something
> labor intensive, am looking for an easier way.
> Thank you in advance.
> Sam

Granting permissions

I have a production server with a few databases. Each database has about
100-150 tables, and 200 or so Stored Procs.
I have a new user john that gets created on each database. I need to give
him the ability to select, insert, update and delete from any user table, an
d
to be able to execute any Stored Procedure.
If I grant this new user membership to db_datareader, he gets select from
any user table.
If I grant this new user membership to db_datawriter, he gets insert,
update, delete to any user table.
So far, so good. Now the problem -
What role can I grant that would allow this user to be able to run any
stored procedure? Note that my manager will not let me grant him db_owner, a
s
this gives him too much rights.
And what every I do to give him the above rights, he can't be allowed to
create tables, create views or create any stored procedures
I could do this manually through the GUI, but with over 200 stored procs per
table, this is too time consuming. I could script, but this is also somethin
g
labor intensive, am looking for an easier way.
Thank you in advance.
SamThink I found the solution. For each table, use the:
DENY CREATE VIEW to john
DENY CREATE TABLE to john
DENY CREATE SP to john
Still not sure how to give a user rights to execute all Stored Procedures,
though.
Sam
"Sam" wrote:

> I have a production server with a few databases. Each database has about
> 100-150 tables, and 200 or so Stored Procs.
> I have a new user john that gets created on each database. I need to give
> him the ability to select, insert, update and delete from any user table,
and
> to be able to execute any Stored Procedure.
> If I grant this new user membership to db_datareader, he gets select from
> any user table.
> If I grant this new user membership to db_datawriter, he gets insert,
> update, delete to any user table.
> So far, so good. Now the problem -
> What role can I grant that would allow this user to be able to run any
> stored procedure? Note that my manager will not let me grant him db_owner,
as
> this gives him too much rights.
> And what every I do to give him the above rights, he can't be allowed to
> create tables, create views or create any stored procedures
> I could do this manually through the GUI, but with over 200 stored procs p
er
> table, this is too time consuming. I could script, but this is also someth
ing
> labor intensive, am looking for an easier way.
> Thank you in advance.
> Sam|||Create a new role in each database.
Grant the appropriate permissions to this role. (I know it's time
consuming, but it's a one time deal).
Then add your new users to this role in each db.
If you check around, there have been several scripts already created that
will grant permissions. You just need to tweak them a bit. My guess is
that in an hour worth of your time, you will have the script completed.
This newsgroup has had several posted to it within the last two weeks.
Rick Sawtell
MCT, MCSD, MCDBA
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:E7839D71-7EC7-47A0-96DF-4A62DAEED1D0@.microsoft.com...
> I have a production server with a few databases. Each database has about
> 100-150 tables, and 200 or so Stored Procs.
> I have a new user john that gets created on each database. I need to give
> him the ability to select, insert, update and delete from any user table,
and
> to be able to execute any Stored Procedure.
> If I grant this new user membership to db_datareader, he gets select from
> any user table.
> If I grant this new user membership to db_datawriter, he gets insert,
> update, delete to any user table.
> So far, so good. Now the problem -
> What role can I grant that would allow this user to be able to run any
> stored procedure? Note that my manager will not let me grant him db_owner,
as
> this gives him too much rights.
> And what every I do to give him the above rights, he can't be allowed to
> create tables, create views or create any stored procedures
> I could do this manually through the GUI, but with over 200 stored procs
per
> table, this is too time consuming. I could script, but this is also
something
> labor intensive, am looking for an easier way.
> Thank you in advance.
> Sam

Granting permissions

I have a production server with a few databases. Each database has about
100-150 tables, and 200 or so Stored Procs.
I have a new user john that gets created on each database. I need to give
him the ability to select, insert, update and delete from any user table, and
to be able to execute any Stored Procedure.
If I grant this new user membership to db_datareader, he gets select from
any user table.
If I grant this new user membership to db_datawriter, he gets insert,
update, delete to any user table.
So far, so good. Now the problem -
What role can I grant that would allow this user to be able to run any
stored procedure? Note that my manager will not let me grant him db_owner, as
this gives him too much rights.
And what every I do to give him the above rights, he can't be allowed to
create tables, create views or create any stored procedures
I could do this manually through the GUI, but with over 200 stored procs per
table, this is too time consuming. I could script, but this is also something
labor intensive, am looking for an easier way.
Thank you in advance.
SamThink I found the solution. For each table, use the:
DENY CREATE VIEW to john
DENY CREATE TABLE to john
DENY CREATE SP to john
Still not sure how to give a user rights to execute all Stored Procedures,
though.
Sam
"Sam" wrote:
> I have a production server with a few databases. Each database has about
> 100-150 tables, and 200 or so Stored Procs.
> I have a new user john that gets created on each database. I need to give
> him the ability to select, insert, update and delete from any user table, and
> to be able to execute any Stored Procedure.
> If I grant this new user membership to db_datareader, he gets select from
> any user table.
> If I grant this new user membership to db_datawriter, he gets insert,
> update, delete to any user table.
> So far, so good. Now the problem -
> What role can I grant that would allow this user to be able to run any
> stored procedure? Note that my manager will not let me grant him db_owner, as
> this gives him too much rights.
> And what every I do to give him the above rights, he can't be allowed to
> create tables, create views or create any stored procedures
> I could do this manually through the GUI, but with over 200 stored procs per
> table, this is too time consuming. I could script, but this is also something
> labor intensive, am looking for an easier way.
> Thank you in advance.
> Sam|||Create a new role in each database.
Grant the appropriate permissions to this role. (I know it's time
consuming, but it's a one time deal).
Then add your new users to this role in each db.
If you check around, there have been several scripts already created that
will grant permissions. You just need to tweak them a bit. My guess is
that in an hour worth of your time, you will have the script completed.
This newsgroup has had several posted to it within the last two weeks.
Rick Sawtell
MCT, MCSD, MCDBA
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:E7839D71-7EC7-47A0-96DF-4A62DAEED1D0@.microsoft.com...
> I have a production server with a few databases. Each database has about
> 100-150 tables, and 200 or so Stored Procs.
> I have a new user john that gets created on each database. I need to give
> him the ability to select, insert, update and delete from any user table,
and
> to be able to execute any Stored Procedure.
> If I grant this new user membership to db_datareader, he gets select from
> any user table.
> If I grant this new user membership to db_datawriter, he gets insert,
> update, delete to any user table.
> So far, so good. Now the problem -
> What role can I grant that would allow this user to be able to run any
> stored procedure? Note that my manager will not let me grant him db_owner,
as
> this gives him too much rights.
> And what every I do to give him the above rights, he can't be allowed to
> create tables, create views or create any stored procedures
> I could do this manually through the GUI, but with over 200 stored procs
per
> table, this is too time consuming. I could script, but this is also
something
> labor intensive, am looking for an easier way.
> Thank you in advance.
> Sam

Granting permissions

How can I do
create proc MyProc
as
--...proc logic
go
grant execute on MyProc to MYCOMPUTER\ASPNET
I can do the 'grant' statement where the user name doesn't include a
computer prefix - but the ASPNET account does! It keeps complaining, citing
'Incorrect syntax near \'.
The following doesn't work either.
grant execute on MyProc to 'MYCOMPUTER\ASPNET'
Any suggestions?Bonj,
I think MYCOMPUTER\ASPNET is the login name, What is the user name
associated to this login in your db?
AMB
"Bonj" wrote:

> How can I do
> create proc MyProc
> as
> --...proc logic
> go
> grant execute on MyProc to MYCOMPUTER\ASPNET
> I can do the 'grant' statement where the user name doesn't include a
> computer prefix - but the ASPNET account does! It keeps complaining, citin
g
> 'Incorrect syntax near '.
> The following doesn't work either.
> grant execute on MyProc to 'MYCOMPUTER\ASPNET'
>
> Any suggestions?|||assuming MYCOMPUTER\ASPNET is a defined login, then:
grant execute on MyProc to "MYCOMPUTER\\ASPNET"
note double quotes
"Bonj" <Bonj@.discussions.microsoft.com> wrote in message
news:C4F7742C-22E5-4FEB-BE1B-3570FC0645B0@.microsoft.com...
| How can I do
|
| create proc MyProc
| as
| --...proc logic
| go
| grant execute on MyProc to MYCOMPUTER\ASPNET
|
| I can do the 'grant' statement where the user name doesn't include a
| computer prefix - but the ASPNET account does! It keeps complaining,
citing
| 'Incorrect syntax near \'.
| The following doesn't work either.
| grant execute on MyProc to 'MYCOMPUTER\ASPNET'
|
|
| Any suggestions?sql

granting permission to create view

What is the best way to grant a user permission to create a view?

I first created a role using enterprise manager but for the role I
created it doesn't seem to offer that permission. It offers the basic
stuff such as insert, select, and update.

I could go in and use a grant create view sql statement I suppose but
I'd rather do it through enterprise manager where it would be visible
if I need to change it in the future.

-Davidwireless (wireless200@.yahoo.com) writes:
> What is the best way to grant a user permission to create a view?
> I first created a role using enterprise manager but for the role I
> created it doesn't seem to offer that permission. It offers the basic
> stuff such as insert, select, and update.
> I could go in and use a grant create view sql statement I suppose but
> I'd rather do it through enterprise manager where it would be visible
> if I need to change it in the future.

Enterprise Manager just reads the information off the database, and if
you say GRANT CREATE VIEW in Query Analyzer it should up in EM. The
permission does not look different because it was created from EM.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Tue, 24 Aug 2004 21:13:30 +0000 (UTC), Erland Sommarskog wrote:

> wireless (wireless200@.yahoo.com) writes:
>> What is the best way to grant a user permission to create a view?
>>
>> I first created a role using enterprise manager but for the role I
>> created it doesn't seem to offer that permission. It offers the basic
>> stuff such as insert, select, and update.
>>
>> I could go in and use a grant create view sql statement I suppose but
>> I'd rather do it through enterprise manager where it would be visible
>> if I need to change it in the future.
> Enterprise Manager just reads the information off the database, and if
> you say GRANT CREATE VIEW in Query Analyzer it should up in EM. The
> permission does not look different because it was created from EM.

It may not be obvious (I had to hunt for it) but the place to grant
statement permissions (to roles, users, or what have you) within Enterprise
Manager, is the Permissions tab of the Properties dialog for the database.|||Ross Presser <rpresser@.imtek.com> wrote in message news:<1xr5t5ei0r7jc.rs9lk0lr4jx1$.dlg@.40tude.net>...

> It may not be obvious (I had to hunt for it) but the place to grant
> statement permissions (to roles, users, or what have you) within Enterprise
> Manager, is the Permissions tab of the Properties dialog for the database.

That's right. I eventually found that. Thanks.

-David

Granting permission to a database user to alter database role

I want a database user to be able to alter login, database user and database role from my application. so, i assigned that user to sccurityadmin server role, db_accessadmin and db_securityadmin database roles....By now, the user can add or remove login and database user. However, the user cannot add or remove any database role membership. What am I missing here? What should I do so that the user can create, and alter database roles in the database?

Membership to db_securityadmin should be enough. Can you post the commands that you attempt and the resulting error message. Also, what version of SQL Server are you using?

Thanks
Laurentiu

Granting permission on multiple stored procs

Is there a better way of granting permission to a stored proc for a selected user other than (enterprise manager) select sp then accessing propertys then permissions, then user?GRANT EXEC ON <proc> TO <MyUser>

just about anything you can do in the EM can be scripted saved as script and used again. which makes you more efficient.|||but the rest of the story is this...
the project is a work in progress thing. we are added new stored procs as needed (up to 83 as of this morning) and now we wanted a selected user to test the new project. I was looking for a way to not have to type out all 83 stored proc names in order to grant access to them for this user. I was hoping for a solution/feature that i was not aware of in EM. so it looks like i will have to do this process manually either way (EM or Script)...|||select 'grant execute on ' + name + ' to user'
from sysobjects
where type = 'P'
and crdate > getdate() -1|||You are using Database Roles, I hope.

granting permission

Want to grant permission for multiple tables to a user or role. is there a w
ay to do this with transact-SQL.
Please help!!!!!
Thanks.
A.SYes, look up GRANT in Books Online.
"J C" <anonymous@.discussions.microsoft.com> wrote in message
news:96B8B085-4A59-45B5-B2F6-1FF3336628FC@.microsoft.com...
> Want to grant permission for multiple tables to a user or role. is there a
way to do this with transact-SQL.
> Please help!!!!!
> Thanks.
> A.Ssql

Granting EXECUTE permissions to all stored procedures

I want to allow my user to have exec permissions on all stored procs in the
database. Is there a quick way to do this? Right now, the only way I know ho
w to do this is to go into the permissions for the user and check the EXEC c
heckbox for each individual
stored proc...Hi
Execute the following in Query Analyzer with text result (Query menu click
result in text) and copy and paste results
to give you the required script.
select 'grant exec on ' + QUOTENAME(name) + ' to [user_name]'
from sysobjects where type = 'P'
and objectproperty(id,'IsMSShipped')=0
Note:
Replace the user_name with actual user name or role name.
Tahnks
Hari
MCDBA
"DBA72" <anonymous@.discussions.microsoft.com> wrote in message
news:77702A18-57F2-4B75-B6AC-B4D769DA8951@.microsoft.com...
> I want to allow my user to have exec permissions on all stored procs in
the database. Is there a quick way to do this? Right now, the only way I
know how to do this is to go into the permissions for the user and check the
EXEC checkbox for each individual stored proc...

Granting EXEC to all my user sprocs in one hit

Hi,
(I'm a web-programmer, having to double as a DBA, so please forgive me if
this is a stupid question!)
I've been trying to determine if it is possible to:
1. Retrieve all my user-created sprocs for a specified dbo (to a temp
table), then
2. Grant the EXEC privilege to these user-sprocs only (excluding the system
sprocs).
OR:
3. Grant the EXEC permission using SEMgr across all my user-sprocs at one go
instead of bashing the daylights out of my spacebar.
I'm trying to achieve this because I have two dbo's: one production and one
dev.
I'm detaching the live copy and copying the files across to dev machine,
then re-attaching them in their respective places, in order to get a
snapshot of the data in the production version across to my dev copy (for
testing report queries and saving me creating a LOAD of fake data).
# because of the relationships and number of tables, exporting data from
master to dev is a PITA, as I'd have to purge all the dev tables and set
identity insert on the export transformation - I suppose I ought to build an
admin-only procedure to do all this... #
Once I've re-attached the dev copy, I've found I have to remove the assigned
built-in account from the dbo's user list and re-add the user. This then
means I've got to add the exec permissions as well, which while not a
difficult task in SEMgr, it is a pain to do this way as I currently have
over 200 sprocs.
I see that the GRANT syntax allows me to set the permission, but only for
one sproc at a time. If I can retrieve a list of the "usp_" prefixed sprocs
for my specific dbo, I could put these in a temp table, then loop this and
set the permissions.
Surely this type of info is available in one of the system tables, as this
must be how the SEMgr permissions dialog is populated?
TIA for any help.
Alec MacLeanYou can run the procedure below (after you have created it in master) in the
context of a user database e.g.
use pubs
go
exec sp_grantexec 'foo',1
-- create the procedure in master
use master
go
create procedure sp_grantexec(@.user sysname,@.debug int = 0)
as
set nocount on
declare @.ret int
declare @.sql nvarchar(4000)
declare @.db sysname ; set @.db = DB_NAME()
declare @.u sysname ; set @.u = QUOTENAME(@.user)
set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM
INFORMATION_SCHEMA.ROUTINES ' +
'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'
'IsMSShipped'') =
0'
if @.debug = 1 print @.sql
exec @.ret = master.dbo.xp_execresultset @.sql,@.db
If @.ret <> 0
begin
raiserror('Error executing command %s',16,1,@.sql)
return -1
end
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Alec MacLean" <alec.maclean@.copeohs.com.NO_SPAM> wrote in message
news:OuU1Sj7KEHA.3332@.TK2MSFTNGP10.phx.gbl...
> Hi,
> (I'm a web-programmer, having to double as a DBA, so please forgive me if
> this is a stupid question!)
> I've been trying to determine if it is possible to:
> 1. Retrieve all my user-created sprocs for a specified dbo (to a temp
> table), then
> 2. Grant the EXEC privilege to these user-sprocs only (excluding the
system
> sprocs).
> OR:
> 3. Grant the EXEC permission using SEMgr across all my user-sprocs at one
go
> instead of bashing the daylights out of my spacebar.
> I'm trying to achieve this because I have two dbo's: one production and
one
> dev.
> I'm detaching the live copy and copying the files across to dev machine,
> then re-attaching them in their respective places, in order to get a
> snapshot of the data in the production version across to my dev copy (for
> testing report queries and saving me creating a LOAD of fake data).
> # because of the relationships and number of tables, exporting data from
> master to dev is a PITA, as I'd have to purge all the dev tables and set
> identity insert on the export transformation - I suppose I ought to build
an
> admin-only procedure to do all this... #
> Once I've re-attached the dev copy, I've found I have to remove the
assigned
> built-in account from the dbo's user list and re-add the user. This then
> means I've got to add the exec permissions as well, which while not a
> difficult task in SEMgr, it is a pain to do this way as I currently have
> over 200 sprocs.
> I see that the GRANT syntax allows me to set the permission, but only for
> one sproc at a time. If I can retrieve a list of the "usp_" prefixed
sprocs
> for my specific dbo, I could put these in a temp table, then loop this and
> set the permissions.
> Surely this type of info is available in one of the system tables, as this
> must be how the SEMgr permissions dialog is populated?
> TIA for any help.
> --
> Alec MacLean
>
>|||Thanks Jasper - looks like exactly what I was looking for.
If I am interpreting correctly, this assigns the passed user id exec
permission on any sproc that didn't ship with the SQL Server install, within
the context of the database (pubs in your example) it is called from?
Regards
Alec MacLean
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:eB1qVA9KEHA.2556@.TK2MSFTNGP11.phx.gbl...
> You can run the procedure below (after you have created it in master) in
the
> context of a user database e.g.
> use pubs
> go
> exec sp_grantexec 'foo',1
> -- create the procedure in master
> use master
> go
> create procedure sp_grantexec(@.user sysname,@.debug int = 0)
> as
> set nocount on
> declare @.ret int
> declare @.sql nvarchar(4000)
> declare @.db sysname ; set @.db = DB_NAME()
> declare @.u sysname ; set @.u = QUOTENAME(@.user)
> set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
> QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM
> INFORMATION_SCHEMA.ROUTINES ' +
> 'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'
'IsMSShipped'') =
> 0'
> if @.debug = 1 print @.sql
> exec @.ret = master.dbo.xp_execresultset @.sql,@.db
> If @.ret <> 0
> begin
> raiserror('Error executing command %s',16,1,@.sql)
> return -1
> end
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Alec MacLean" <alec.maclean@.copeohs.com.NO_SPAM> wrote in message
> news:OuU1Sj7KEHA.3332@.TK2MSFTNGP10.phx.gbl...
if[vbcol=seagreen]
> system
one[vbcol=seagreen]
> go
> one
(for[vbcol=seagreen]
build[vbcol=seagreen]
> an
> assigned
then[vbcol=seagreen]
for[vbcol=seagreen]
> sprocs
and[vbcol=seagreen]
this[vbcol=seagreen]
>|||You got it :-)
xp_execresultset runs the dynamic sql to generate the commands and then
executes them
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Alec MacLean" <alec.maclean@.copeohs.com.NO_SPAM> wrote in message
news:OXgFCuELEHA.1348@.TK2MSFTNGP12.phx.gbl...
> Thanks Jasper - looks like exactly what I was looking for.
> If I am interpreting correctly, this assigns the passed user id exec
> permission on any sproc that didn't ship with the SQL Server install,
within
> the context of the database (pubs in your example) it is called from?
> Regards
> --
> Alec MacLean
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:eB1qVA9KEHA.2556@.TK2MSFTNGP11.phx.gbl...
> the
+[vbcol=seagreen]
=[vbcol=seagreen]
> if
> one
and[vbcol=seagreen]
machine,[vbcol=seagreen]
> (for
from[vbcol=seagreen]
set[vbcol=seagreen]
> build
> then
have[vbcol=seagreen]
> for
> and
> this
>|||You should be careful when you use xp_execresultset as it contains buffer
overflow.
The xp_execresultset extended stored procedure does not properly allocate
enough memory when called with a long string as the first parameter...
https://www.appsecinc.com/Policy/PolicyCheck2018.html
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OTITtXHLEHA.2704@.TK2MSFTNGP10.phx.gbl...
> You got it :-)
> xp_execresultset runs the dynamic sql to generate the commands and then
> executes them
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Alec MacLean" <alec.maclean@.copeohs.com.NO_SPAM> wrote in message
> news:OXgFCuELEHA.1348@.TK2MSFTNGP12.phx.gbl...
> within
in[vbcol=seagreen]
''.''[vbcol=seagreen]
> +
OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'
'IsMSShipped'')[vbcol=seagreen]
> =
me[vbcol=seagreen]
temp[vbcol=seagreen]
at[vbcol=seagreen]
> and
> machine,
> from
> set
a[vbcol=seagreen]
> have
only[vbcol=seagreen]
prefixed[vbcol=seagreen]
this[vbcol=seagreen]
as[vbcol=seagreen]
>|||True but the issue was fixed in a patch over 3 years ago.
-Sue
On Wed, 28 Apr 2004 10:55:51 -0400, "joe"
<pearl_77@.hotmail.com> wrote:

>You should be careful when you use xp_execresultset as it contains buffer
>overflow.
>The xp_execresultset extended stored procedure does not properly allocate
>enough memory when called with a long string as the first parameter...
>
>https://www.appsecinc.com/Policy/PolicyCheck2018.html
>"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
>news:OTITtXHLEHA.2704@.TK2MSFTNGP10.phx.gbl...
>in
>''.''
> OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'
'IsMSShipped'')
>me
>temp
>at
>a
>only
>prefixed
>this
>as
>|||Thanks to all for the warning info.
Alec MacLean

>"joe"
> "Sue Hoegemeier"
>"Jasper Smith"

Granting Edit Permission on Stored Procedures

I have a user on my database that has the following base permissions :

public
db_datareader

I need to give this user permission to edit a single stored procedure. I have tried using the following command :

GRANT ALL ON stored_procedure_name TO username

Which executes successfully, but the user still cannot edit the stored procedure.

If I give the user db_ddladmin permission they can edit all the user stored procedures, but for security reasons I would prefer to be able to this this at procedure level rather than a global permission on all user procs.

Does anybody know how I can do this?

EDIT : This is on SQL 2000Does this user have the same permission on some tables which related to this procedure?sql

Granting db_datareader permissions

Is there a way (Probably a script) to
grant 'db_datareader' permission to a Windows user
(SERVER\username) without the GUI '.
Thanks.You can use sp_addrolemember.
You can find more information on sp_addrolemember in books
online.
-Sue
On Mon, 5 Apr 2004 08:05:51 -0700, "Eric"
<anonymous@.discussions.microsoft.com> wrote:

>Is there a way (Probably a script) to
>grant 'db_datareader' permission to a Windows user
>(SERVER\username) without the GUI '.
>Thanks.|||That is for user defined roles NOT for fixed database
roles.

>--Original Message--
>You can use sp_addrolemember.
>You can find more information on sp_addrolemember in books
>online.
>-Sue
>On Mon, 5 Apr 2004 08:05:51 -0700, "Eric"
><anonymous@.discussions.microsoft.com> wrote:
>
>.
>|||Sorry......My bet. I had a syntax error......

>--Original Message--
>You can use sp_addrolemember.
>You can find more information on sp_addrolemember in books
>online.
>-Sue
>On Mon, 5 Apr 2004 08:05:51 -0700, "Eric"
><anonymous@.discussions.microsoft.com> wrote:
>
>.
>|||No...it's for any existing database role.
-Sue
On Mon, 5 Apr 2004 08:23:27 -0700, "Eric"
<anonymous@.discussions.microsoft.com> wrote:
>That is for user defined roles NOT for fixed database
>roles.
>

Granting create table to database role

Hi All,
You'll have to forgive me for not knowing too much about SQL Server
security. Here is the scenario:
I have a user called FDS, which owns the database and all user tables. I
have two database roles, one FDS_User which has select/update permissions on
all tables, and one FDS_Admin. I want any users with the FDS_Admin role to
be able to modify the structure of tables (actually only three of them but
I'll settle for all if it's easier). The user 'ryan' has both roles, and
can select from any tables, but can't create/modify them. The create
statement returns "Specified owner name 'fds' either does not exist or you
do not have permission...", and the alter table statement returns "User does
not have permission to perform this operation on table 'Ryan'."
In enterprise manager -> database properties -> permissions, the FDS_Admin
role has a tick in the Create Table box.
Does anyone have any ideas how I can get the above to work? Let me know if
you need any more information.
Thanks in advance
RyanHi
Add these users to db_owner database fixed role.
"Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
news:eX1JIoU0EHA.752@.TK2MSFTNGP12.phx.gbl...
> Hi All,
> You'll have to forgive me for not knowing too much about SQL Server
> security. Here is the scenario:
> I have a user called FDS, which owns the database and all user tables. I
> have two database roles, one FDS_User which has select/update permissions
on
> all tables, and one FDS_Admin. I want any users with the FDS_Admin role
to
> be able to modify the structure of tables (actually only three of them but
> I'll settle for all if it's easier). The user 'ryan' has both roles, and
> can select from any tables, but can't create/modify them. The create
> statement returns "Specified owner name 'fds' either does not exist or you
> do not have permission...", and the alter table statement returns "User
does
> not have permission to perform this operation on table 'Ryan'."
> In enterprise manager -> database properties -> permissions, the FDS_Admin
> role has a tick in the Create Table box.
> Does anyone have any ideas how I can get the above to work? Let me know
if
> you need any more information.
> Thanks in advance
> Ryan
>|||Thanks,
That has worked, but I've noticed that I can remove the FDS_Admin role, and
the user can still create/alter tables. Is this the only way to do this?
I'll have a look into what else db_owner can do but it seems a bit risky to
add every user to the db_owner role. Any thoughts?
Thanks again
Ryan
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eIt49AW0EHA.3452@.TK2MSFTNGP14.phx.gbl...
> Hi
> Add these users to db_owner database fixed role.
>
> "Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
> news:eX1JIoU0EHA.752@.TK2MSFTNGP12.phx.gbl...
> on
> to
> does
> if
>|||Hi
In our company we gave permissions only for EXECUTION on stored procedures
,not on underlaying tables.
If you want the users to be able SELECT/UPDATE/DELETE/INSERT don't grant
them permissions on actual tables ,instead create stored procedures that
will manipulate against tables.
"Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
news:uIfKVIW0EHA.3336@.TK2MSFTNGP11.phx.gbl...
> Thanks,
> That has worked, but I've noticed that I can remove the FDS_Admin role,
and
> the user can still create/alter tables. Is this the only way to do this?
> I'll have a look into what else db_owner can do but it seems a bit risky
to
> add every user to the db_owner role. Any thoughts?
> Thanks again
> Ryan
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eIt49AW0EHA.3452@.TK2MSFTNGP14.phx.gbl...
I[vbcol=seagreen]
permissions[vbcol=seagreen]
role[vbcol=seagreen]
and[vbcol=seagreen]
know[vbcol=seagreen]
>|||Unfortunately the application has been designed and written. It might be
some work to change it all now! Surely there is an easy way to create a
role or otherwise which will allow users to add or modify tables, but not
give them full db_owner access?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ud5F0YW0EHA.3468@.TK2MSFTNGP14.phx.gbl...
> Hi
> In our company we gave permissions only for EXECUTION on stored procedures
> ,not on underlaying tables.
> If you want the users to be able SELECT/UPDATE/DELETE/INSERT don't grant
> them permissions on actual tables ,instead create stored procedures that
> will manipulate against tables.
>
>
>
> "Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
> news:uIfKVIW0EHA.3336@.TK2MSFTNGP11.phx.gbl...
> and
> to
> I
> permissions
> role
> and
> know
>|||Hello again,
Having looked through the help, I think what I want is a db_ddladmin. I
believe this will allow any users to add/modify tables....
"Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
news:eJfPlgW0EHA.3244@.TK2MSFTNGP10.phx.gbl...
> Unfortunately the application has been designed and written. It might be
> some work to change it all now! Surely there is an easy way to create a
> role or otherwise which will allow users to add or modify tables, but not
> give them full db_owner access?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ud5F0YW0EHA.3468@.TK2MSFTNGP14.phx.gbl...
>|||Hi
Look at db_datawriter,db_datareader fixed database role in the BOL.
"Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
news:eJfPlgW0EHA.3244@.TK2MSFTNGP10.phx.gbl...
> Unfortunately the application has been designed and written. It might be
> some work to change it all now! Surely there is an easy way to create a
> role or otherwise which will allow users to add or modify tables, but not
> give them full db_owner access?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ud5F0YW0EHA.3468@.TK2MSFTNGP14.phx.gbl...
procedures[vbcol=seagreen]
this?[vbcol=seagreen]
risky[vbcol=seagreen]
tables.[vbcol=seagreen]
them[vbcol=seagreen]
roles,[vbcol=seagreen]
create[vbcol=seagreen]
or[vbcol=seagreen]
>

Granting CREATE DATABASE rights

Can anyone give me some insight into how to grant CREATE
DATABASE rights to a user, but without him/her abled to
look at other objects.
For example, by granting this right, it seems this users
is abled to view all the logins etc within Enterprise
Managers (SQL7).
And wot would be the implications, since Access Xp
Projects required CREATE DATABASE rights in order to
create a new project? Is there another way?
Thks.
WayneDid you try
GRANT CREATE DATABASE TO loginname
Or giving the login the dbcreator role?
Note, however that this makes the login the owner of that database
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Wayne" <Wayne.Chan@.Bradford.NHS.UK> wrote in message
news:02ba01c3a901$5cda2ed0$a501280a@.phx.gbl...
> Can anyone give me some insight into how to grant CREATE
> DATABASE rights to a user, but without him/her abled to
> look at other objects.
> For example, by granting this right, it seems this users
> is abled to view all the logins etc within Enterprise
> Managers (SQL7).
> And wot would be the implications, since Access Xp
> Projects required CREATE DATABASE rights in order to
> create a new project? Is there another way?
> Thks.
> Waynesql