Defining custom Roles with limited access to SQL Objects

Hi!

I'm assisting in the creation of a development enviroment with SQL Server 2005, and I need to assign some custom roles, in particular, a Stored Developer Role should be able to create, modify and execute Stored Procedures but they should not be able to alter tables or views, but should be able to retrieve/insert data from those tables.

I've tried with the default roles in 2005 to no avail.

Is there a relatively easy way to accomplish this with a database alredy populated with objects of both kinds? (SP's and Tables / Views)

Thanks!

You should create your own role and assign the required permissions to it. See CREATE ROLE for information on how to create a role.

Thanks
Laurentiu

default value

is it possible to give a default value for a column which is generated as a result of left outer join...

when there is no value for a particular column it shows null is it possible to change give a default value based on its data type?

Yes, you can use the IsNull function e.g.

ISNULL(ColumnName, ReplacementValue)

|||

ok let me try and see whether i can get that working

|||

thanks for the help it worked

|||

No problem

default user for object, sysadmin role, KB22067, invalid object

Trying to select rows from a table with a particular owner
which has unfortunately been set to the sysadmin role for
sqlserver by some predecessor.
'select * from mytable' gives error message 'invalid object'
This is an inconvenience
because we have that owner name hardcoded in an unknown
number of places in the code, and many other places queries are
coded in without any owner reference.
In reference to KB255067 it gives two unrealistic workarounds
* don't set the owner in that role
(Waiting for Microsoft TimeMachine(TM) to come out)
* create all the tables with DBO owner
My colleague has invented a workaround where we set the
sysusers.sid to the user we want it to be instead of to 0x01
but I am unsure of the consequences to that.
I would like to know what a truly useable workaround is
that will not have weird consequences later on so that
SqlServer will do what I want it to do.
For example a real workaround would be
* Mark up that user so the evidence of it having been in
the sysadmin role is removed
or in the wording of the KB article
* reconstruct or fix the ownership chain (since it was broken
when the sysadmin role was used)
DO NOT MODIFY SYSTEM TABLES DIRECTLY! Yes, a "qualified" dba could "get
away" with modifying system tables, but merely changing the owner sid is
insufficient as it will not modify the sysdepends table references.
Use sp_changeobjectowner and modify the owner of the object to dbo. If the
old owner was qualified and hard-coded, then map the application login, or
another login, to an aliased user to that old owner name.
A final consideration would be to script out all of those objects, export
the data, drop the objects, and then recreate the objects under a legitimate
database user (note, not login). Then, reimport the data.
Sincerely,
Anthony Thomas

"ElvinKee" <ElvinKee@.discussions.microsoft.com> wrote in message
news:D09D8C22-AD0D-4DBD-81DC-42EACF0CC07E@.microsoft.com...
> Trying to select rows from a table with a particular owner
> which has unfortunately been set to the sysadmin role for
> sqlserver by some predecessor.
> 'select * from mytable' gives error message 'invalid object'
> This is an inconvenience
> because we have that owner name hardcoded in an unknown
> number of places in the code, and many other places queries are
> coded in without any owner reference.
> In reference to KB255067 it gives two unrealistic workarounds
> * don't set the owner in that role
> (Waiting for Microsoft TimeMachine(TM) to come out)
> * create all the tables with DBO owner
> My colleague has invented a workaround where we set the
> sysusers.sid to the user we want it to be instead of to 0x01
> but I am unsure of the consequences to that.
> I would like to know what a truly useable workaround is
> that will not have weird consequences later on so that
> SqlServer will do what I want it to do.
> For example a real workaround would be
> * Mark up that user so the evidence of it having been in
> the sysadmin role is removed
> or in the wording of the KB article
> * reconstruct or fix the ownership chain (since it was broken
> when the sysadmin role was used)
>
>
> --

default user for object, sysadmin role, KB22067, invalid object

Trying to select rows from a table with a particular owner
which has unfortunately been set to the sysadmin role for
sqlserver by some predecessor.
'select * from mytable' gives error message 'invalid object'
This is an inconvenience
because we have that owner name hardcoded in an unknown
number of places in the code, and many other places queries are
coded in without any owner reference.
In reference to KB255067 it gives two unrealistic workarounds
* don't set the owner in that role
(Waiting for Microsoft TimeMachine(TM) to come out)
* create all the tables with DBO owner
My colleague has invented a workaround where we set the
sysusers.sid to the user we want it to be instead of to 0x01
but I am unsure of the consequences to that.
I would like to know what a truly useable workaround is
that will not have weird consequences later on so that
SqlServer will do what I want it to do.
For example a real workaround would be
* Mark up that user so the evidence of it having been in
the sysadmin role is removed
or in the wording of the KB article
* reconstruct or fix the ownership chain (since it was broken
when the sysadmin role was used)DO NOT MODIFY SYSTEM TABLES DIRECTLY! Yes, a "qualified" dba could "get
away" with modifying system tables, but merely changing the owner sid is
insufficient as it will not modify the sysdepends table references.
Use sp_changeobjectowner and modify the owner of the object to dbo. If the
old owner was qualified and hard-coded, then map the application login, or
another login, to an aliased user to that old owner name.
A final consideration would be to script out all of those objects, export
the data, drop the objects, and then recreate the objects under a legitimate
database user (note, not login). Then, reimport the data.
Sincerely,
Anthony Thomas
"ElvinKee" <ElvinKee@.discussions.microsoft.com> wrote in message
news:D09D8C22-AD0D-4DBD-81DC-42EACF0CC07E@.microsoft.com...
> Trying to select rows from a table with a particular owner
> which has unfortunately been set to the sysadmin role for
> sqlserver by some predecessor.
> 'select * from mytable' gives error message 'invalid object'
> This is an inconvenience
> because we have that owner name hardcoded in an unknown
> number of places in the code, and many other places queries are
> coded in without any owner reference.
> In reference to KB255067 it gives two unrealistic workarounds
> * don't set the owner in that role
> (Waiting for Microsoft TimeMachine(TM) to come out)
> * create all the tables with DBO owner
> My colleague has invented a workaround where we set the
> sysusers.sid to the user we want it to be instead of to 0x01
> but I am unsure of the consequences to that.
> I would like to know what a truly useable workaround is
> that will not have weird consequences later on so that
> SqlServer will do what I want it to do.
> For example a real workaround would be
> * Mark up that user so the evidence of it having been in
> the sysadmin role is removed
> or in the wording of the KB article
> * reconstruct or fix the ownership chain (since it was broken
> when the sysadmin role was used)
>
>
> --

default render

Is there a way within rs2000 to set the default render to pdf for a particular report? I have a report which asks for parameters which will be exported to pdf before printing. I want the report to go straight to pdf when 'view report' is clicked rather than http.No, I don′t think that this is possible. (Didn′t see any switched or tweaks for that).

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de