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 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

Granter does not have GRANT permission

As a contract database application developer I created a SQL database 5 years
ago for a single PC use application. We recently upgraded to a multi-user
environment and I handed the database to the IT department which installed it
on one of their Servers. 3 user group levels were created, and I assigned
rights to all the objects according to the user-group needs. Everything
worked fine for all the users until I started adding new objects and assigned
rights to those objects. When users (that are not part of the group that has
dbo rights) now try to access any newly created object they get this message:
"Granter does not have GRANT permission". The IT department SQL manager is
clueless and useless. I'm clueless too, but I need to find the solution. Any
and all suggestions are very much appreciated.
Thank you,
polarbear
That means the user running the GRANT command doesn't have permissions to
GRANT permissions on the object in question. If I remember right, the
GRANTER must be an administrator or have been granted right from the WITH
GRANT OPTION of the GRANT statement.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"polabear" <polabear@.spitzbergen.com> wrote in message
news:1E70AF39-93F5-49AC-93F5-198AB824B395@.microsoft.com...
> As a contract database application developer I created a SQL database 5
> years
> ago for a single PC use application. We recently upgraded to a multi-user
> environment and I handed the database to the IT department which installed
> it
> on one of their Servers. 3 user group levels were created, and I assigned
> rights to all the objects according to the user-group needs. Everything
> worked fine for all the users until I started adding new objects and
> assigned
> rights to those objects. When users (that are not part of the group that
> has
> dbo rights) now try to access any newly created object they get this
> message:
> "Granter does not have GRANT permission". The IT department SQL manager is
> clueless and useless. I'm clueless too, but I need to find the solution.
> Any
> and all suggestions are very much appreciated.
> Thank you,
> polarbear
|||Roger,
Thanks for the reply. I've been told by the IT person that my login is part
of the "System Adminstrators", so it sounds like somebody needs to add the
"WITH GRANT OPTION" to my login.
Thanks,
polarbear
"Roger Wolter[MSFT]" wrote:

> That means the user running the GRANT command doesn't have permissions to
> GRANT permissions on the object in question. If I remember right, the
> GRANTER must be an administrator or have been granted right from the WITH
> GRANT OPTION of the GRANT statement.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "polabear" <polabear@.spitzbergen.com> wrote in message
> news:1E70AF39-93F5-49AC-93F5-198AB824B395@.microsoft.com...
>
>
|||Hi
No, If you are a member of sysadmin server role you don't need to add "WITH
GRANT OPTION of the GRANT statement"
"polabear" <polabear@.spitzbergen.com> wrote in message
news:C3F47EDB-FE22-45EE-B190-A3F17A39C678@.microsoft.com...[vbcol=seagreen]
> Roger,
> Thanks for the reply. I've been told by the IT person that my login is
> part
> of the "System Adminstrators", so it sounds like somebody needs to add the
> "WITH GRANT OPTION" to my login.
> Thanks,
> polarbear
>
> "Roger Wolter[MSFT]" wrote:
|||> When users (that are not part of the group that has
> dbo rights) now try to access any newly created object they get this
> message:
> "Granter does not have GRANT permission".
It seems your application is doing a bit more than simply accessing objects.
This message implies that users are attempting to GRANT permissions via your
application. In that case, your users need the WITH GRANT. Without that
permission, in order to grant permissions the user must either own the
object, be a db_owner role member, db_securityadmin role member, the
database owner or sysadmin role member.
Happy Holidays
Dan Guzman
SQL Server MVP
"polabear" <polabear@.spitzbergen.com> wrote in message
news:1E70AF39-93F5-49AC-93F5-198AB824B395@.microsoft.com...
> As a contract database application developer I created a SQL database 5
> years
> ago for a single PC use application. We recently upgraded to a multi-user
> environment and I handed the database to the IT department which installed
> it
> on one of their Servers. 3 user group levels were created, and I assigned
> rights to all the objects according to the user-group needs. Everything
> worked fine for all the users until I started adding new objects and
> assigned
> rights to those objects. When users (that are not part of the group that
> has
> dbo rights) now try to access any newly created object they get this
> message:
> "Granter does not have GRANT permission". The IT department SQL manager is
> clueless and useless. I'm clueless too, but I need to find the solution.
> Any
> and all suggestions are very much appreciated.
> Thank you,
> polarbear
|||Im not much of an expert yet but it sounds to me that you are dealing with a
broken ownership chain on a view. From my understanding, this is messy to
resolve. The owner of each object n a view has to give permissions to their
view. The way you avoid these problems is when you are actually creating the
view, by making the DBO owner of everything, Instead of just saying create
view (name) AS you say create view dbo.(name) AS. Too late for this kind of
help I guess but at least if you are working with a view you have an idea of
what may be causing this.
Message posted via http://www.droptable.com

Granter does not have GRANT permission

As a contract database application developer I created a SQL database 5 year
s
ago for a single PC use application. We recently upgraded to a multi-user
environment and I handed the database to the IT department which installed i
t
on one of their Servers. 3 user group levels were created, and I assigned
rights to all the objects according to the user-group needs. Everything
worked fine for all the users until I started adding new objects and assigne
d
rights to those objects. When users (that are not part of the group that has
dbo rights) now try to access any newly created object they get this message
:
"Granter does not have GRANT permission". The IT department SQL manager is
clueless and useless. I'm clueless too, but I need to find the solution. Any
and all suggestions are very much appreciated.
Thank you,
polarbearThat means the user running the GRANT command doesn't have permissions to
GRANT permissions on the object in question. If I remember right, the
GRANTER must be an administrator or have been granted right from the WITH
GRANT OPTION of the GRANT statement.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"polabear" <polabear@.spitzbergen.com> wrote in message
news:1E70AF39-93F5-49AC-93F5-198AB824B395@.microsoft.com...
> As a contract database application developer I created a SQL database 5
> years
> ago for a single PC use application. We recently upgraded to a multi-user
> environment and I handed the database to the IT department which installed
> it
> on one of their Servers. 3 user group levels were created, and I assigned
> rights to all the objects according to the user-group needs. Everything
> worked fine for all the users until I started adding new objects and
> assigned
> rights to those objects. When users (that are not part of the group that
> has
> dbo rights) now try to access any newly created object they get this
> message:
> "Granter does not have GRANT permission". The IT department SQL manager is
> clueless and useless. I'm clueless too, but I need to find the solution.
> Any
> and all suggestions are very much appreciated.
> Thank you,
> polarbear|||Roger,
Thanks for the reply. I've been told by the IT person that my login is part
of the "System Adminstrators", so it sounds like somebody needs to add the
"WITH GRANT OPTION" to my login.
Thanks,
polarbear
"Roger Wolter[MSFT]" wrote:

> That means the user running the GRANT command doesn't have permissions to
> GRANT permissions on the object in question. If I remember right, the
> GRANTER must be an administrator or have been granted right from the WITH
> GRANT OPTION of the GRANT statement.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "polabear" <polabear@.spitzbergen.com> wrote in message
> news:1E70AF39-93F5-49AC-93F5-198AB824B395@.microsoft.com...
>
>|||Hi
No, If you are a member of sysadmin server role you don't need to add "WITH
GRANT OPTION of the GRANT statement"
"polabear" <polabear@.spitzbergen.com> wrote in message
news:C3F47EDB-FE22-45EE-B190-A3F17A39C678@.microsoft.com...[vbcol=seagreen]
> Roger,
> Thanks for the reply. I've been told by the IT person that my login is
> part
> of the "System Adminstrators", so it sounds like somebody needs to add the
> "WITH GRANT OPTION" to my login.
> Thanks,
> polarbear
>
> "Roger Wolter[MSFT]" wrote:
>|||> When users (that are not part of the group that has
> dbo rights) now try to access any newly created object they get this
> message:
> "Granter does not have GRANT permission".
It seems your application is doing a bit more than simply accessing objects.
This message implies that users are attempting to GRANT permissions via your
application. In that case, your users need the WITH GRANT. Without that
permission, in order to grant permissions the user must either own the
object, be a db_owner role member, db_securityadmin role member, the
database owner or sysadmin role member.
Happy Holidays
Dan Guzman
SQL Server MVP
"polabear" <polabear@.spitzbergen.com> wrote in message
news:1E70AF39-93F5-49AC-93F5-198AB824B395@.microsoft.com...
> As a contract database application developer I created a SQL database 5
> years
> ago for a single PC use application. We recently upgraded to a multi-user
> environment and I handed the database to the IT department which installed
> it
> on one of their Servers. 3 user group levels were created, and I assigned
> rights to all the objects according to the user-group needs. Everything
> worked fine for all the users until I started adding new objects and
> assigned
> rights to those objects. When users (that are not part of the group that
> has
> dbo rights) now try to access any newly created object they get this
> message:
> "Granter does not have GRANT permission". The IT department SQL manager is
> clueless and useless. I'm clueless too, but I need to find the solution.
> Any
> and all suggestions are very much appreciated.
> Thank you,
> polarbear|||Im not much of an expert yet but it sounds to me that you are dealing with a
broken ownership chain on a view. From my understanding, this is messy to
resolve. The owner of each object n a view has to give permissions to their
view. The way you avoid these problems is when you are actually creating the
view, by making the DBO owner of everything, Instead of just saying create
view (name) AS you say create view dbo.(name) AS. Too late for this kind of
help I guess but at least if you are working with a view you have an idea of
what may be causing this.
Message posted via http://www.droptable.com

Granter does not have GRANT permission

As a contract database application developer I created a SQL database 5 years
ago for a single PC use application. We recently upgraded to a multi-user
environment and I handed the database to the IT department which installed it
on one of their Servers. 3 user group levels were created, and I assigned
rights to all the objects according to the user-group needs. Everything
worked fine for all the users until I started adding new objects and assigned
rights to those objects. When users (that are not part of the group that has
dbo rights) now try to access any newly created object they get this message:
"Granter does not have GRANT permission". The IT department SQL manager is
clueless and useless. I'm clueless too, but I need to find the solution. Any
and all suggestions are very much appreciated.
Thank you,
polarbearThat means the user running the GRANT command doesn't have permissions to
GRANT permissions on the object in question. If I remember right, the
GRANTER must be an administrator or have been granted right from the WITH
GRANT OPTION of the GRANT statement.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"polabear" <polabear@.spitzbergen.com> wrote in message
news:1E70AF39-93F5-49AC-93F5-198AB824B395@.microsoft.com...
> As a contract database application developer I created a SQL database 5
> years
> ago for a single PC use application. We recently upgraded to a multi-user
> environment and I handed the database to the IT department which installed
> it
> on one of their Servers. 3 user group levels were created, and I assigned
> rights to all the objects according to the user-group needs. Everything
> worked fine for all the users until I started adding new objects and
> assigned
> rights to those objects. When users (that are not part of the group that
> has
> dbo rights) now try to access any newly created object they get this
> message:
> "Granter does not have GRANT permission". The IT department SQL manager is
> clueless and useless. I'm clueless too, but I need to find the solution.
> Any
> and all suggestions are very much appreciated.
> Thank you,
> polarbear|||Roger,
Thanks for the reply. I've been told by the IT person that my login is part
of the "System Adminstrators", so it sounds like somebody needs to add the
"WITH GRANT OPTION" to my login.
Thanks,
polarbear
"Roger Wolter[MSFT]" wrote:
> That means the user running the GRANT command doesn't have permissions to
> GRANT permissions on the object in question. If I remember right, the
> GRANTER must be an administrator or have been granted right from the WITH
> GRANT OPTION of the GRANT statement.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "polabear" <polabear@.spitzbergen.com> wrote in message
> news:1E70AF39-93F5-49AC-93F5-198AB824B395@.microsoft.com...
> > As a contract database application developer I created a SQL database 5
> > years
> > ago for a single PC use application. We recently upgraded to a multi-user
> > environment and I handed the database to the IT department which installed
> > it
> > on one of their Servers. 3 user group levels were created, and I assigned
> > rights to all the objects according to the user-group needs. Everything
> > worked fine for all the users until I started adding new objects and
> > assigned
> > rights to those objects. When users (that are not part of the group that
> > has
> > dbo rights) now try to access any newly created object they get this
> > message:
> > "Granter does not have GRANT permission". The IT department SQL manager is
> > clueless and useless. I'm clueless too, but I need to find the solution.
> > Any
> > and all suggestions are very much appreciated.
> > Thank you,
> > polarbear
>
>|||Hi
No, If you are a member of sysadmin server role you don't need to add "WITH
GRANT OPTION of the GRANT statement"
"polabear" <polabear@.spitzbergen.com> wrote in message
news:C3F47EDB-FE22-45EE-B190-A3F17A39C678@.microsoft.com...
> Roger,
> Thanks for the reply. I've been told by the IT person that my login is
> part
> of the "System Adminstrators", so it sounds like somebody needs to add the
> "WITH GRANT OPTION" to my login.
> Thanks,
> polarbear
>
> "Roger Wolter[MSFT]" wrote:
>> That means the user running the GRANT command doesn't have permissions to
>> GRANT permissions on the object in question. If I remember right, the
>> GRANTER must be an administrator or have been granted right from the WITH
>> GRANT OPTION of the GRANT statement.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Use of included script samples are subject to the terms specified at
>> http://www.microsoft.com/info/cpyright.htm
>> "polabear" <polabear@.spitzbergen.com> wrote in message
>> news:1E70AF39-93F5-49AC-93F5-198AB824B395@.microsoft.com...
>> > As a contract database application developer I created a SQL database 5
>> > years
>> > ago for a single PC use application. We recently upgraded to a
>> > multi-user
>> > environment and I handed the database to the IT department which
>> > installed
>> > it
>> > on one of their Servers. 3 user group levels were created, and I
>> > assigned
>> > rights to all the objects according to the user-group needs. Everything
>> > worked fine for all the users until I started adding new objects and
>> > assigned
>> > rights to those objects. When users (that are not part of the group
>> > that
>> > has
>> > dbo rights) now try to access any newly created object they get this
>> > message:
>> > "Granter does not have GRANT permission". The IT department SQL manager
>> > is
>> > clueless and useless. I'm clueless too, but I need to find the
>> > solution.
>> > Any
>> > and all suggestions are very much appreciated.
>> > Thank you,
>> > polarbear
>>|||> When users (that are not part of the group that has
> dbo rights) now try to access any newly created object they get this
> message:
> "Granter does not have GRANT permission".
It seems your application is doing a bit more than simply accessing objects.
This message implies that users are attempting to GRANT permissions via your
application. In that case, your users need the WITH GRANT. Without that
permission, in order to grant permissions the user must either own the
object, be a db_owner role member, db_securityadmin role member, the
database owner or sysadmin role member.
--
Happy Holidays
Dan Guzman
SQL Server MVP
"polabear" <polabear@.spitzbergen.com> wrote in message
news:1E70AF39-93F5-49AC-93F5-198AB824B395@.microsoft.com...
> As a contract database application developer I created a SQL database 5
> years
> ago for a single PC use application. We recently upgraded to a multi-user
> environment and I handed the database to the IT department which installed
> it
> on one of their Servers. 3 user group levels were created, and I assigned
> rights to all the objects according to the user-group needs. Everything
> worked fine for all the users until I started adding new objects and
> assigned
> rights to those objects. When users (that are not part of the group that
> has
> dbo rights) now try to access any newly created object they get this
> message:
> "Granter does not have GRANT permission". The IT department SQL manager is
> clueless and useless. I'm clueless too, but I need to find the solution.
> Any
> and all suggestions are very much appreciated.
> Thank you,
> polarbear|||Im not much of an expert yet but it sounds to me that you are dealing with a
broken ownership chain on a view. From my understanding, this is messy to
resolve. The owner of each object n a view has to give permissions to their
view. The way you avoid these problems is when you are actually creating the
view, by making the DBO owner of everything, Instead of just saying create
view (name) AS you say create view dbo.(name) AS. Too late for this kind of
help I guess but at least if you are working with a view you have an idea of
what may be causing this.
--
Message posted via http://www.sqlmonster.com

Grant, Revoke, Deny

Context: SQLServer 2000
I have a single table in the database that I want role "Apps" to have SELECT
access only, so using the UI, I find the table and check the SELECT column
for the table and choose the X (to deny) for INSERT, UPDATE, DELETE.
Next, I want to ensure that no one that has public rights to the database
can do anything with this table. First time through I put an X (to deny) for
all four permissions, which prevented everyone (including sa) from
selecting, etc. I found this to be 'by design' behavior so I using the Query
Analyzer to issue the REVOKE method on the table for all 4 items. sa was
then able to still do everything.
My problem is that anyone in the db_datawriter role (or db_datareader) can
still write (read) from the table. I am unable to reassign permissions to
special roles.
I guess I could not use the special role for my datareaders/writers but the
advantage of these roles is that I don't have to continuously go and grant
permissions. If this is my only option in this scenario, then I guess I will
have to go with it - I was just hoping there was something more elegant.
Thanks in advancePermissions are cumulative and DENY takes precedence. REVOKE removes GRANT
and DENY permissions previously assigned. Because all users are members of
public, you should DENY permissions to public only when you want to prohibit
access by all database users.
'sa' is a member of the sysadmin fixed server role and permissions are not
checked for sysadmin role members. I believe you are mistaken when you say
that you prevented 'sa' from accessing the table.
One method to allow Apps users to SELECT from the table and prevent other
'db_datareader' and 'db_datawriter' access:
1) GRANT SELECT ON YourTable TO Apps
2) DENY INSERT, UPDATE, DELETE ON YourTable TO Apps
3) create a new role to prevent access (e.g. 'DenyCertainTables')
4) DENY ALL ON YourTable TO DenyCertainTables
5) add all 'db_datareader' and 'db_datawriter' role members, except Apps
role members, to the 'DenyCertainTables' role.
Hope this helps.
Dan Guzman
SQL Server MVP
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:uqHH2W8tFHA.3528@.TK2MSFTNGP15.phx.gbl...
> Context: SQLServer 2000
> I have a single table in the database that I want role "Apps" to have
> SELECT
> access only, so using the UI, I find the table and check the SELECT column
> for the table and choose the X (to deny) for INSERT, UPDATE, DELETE.
> Next, I want to ensure that no one that has public rights to the database
> can do anything with this table. First time through I put an X (to deny)
> for
> all four permissions, which prevented everyone (including sa) from
> selecting, etc. I found this to be 'by design' behavior so I using the
> Query
> Analyzer to issue the REVOKE method on the table for all 4 items. sa was
> then able to still do everything.
> My problem is that anyone in the db_datawriter role (or db_datareader) can
> still write (read) from the table. I am unable to reassign permissions to
> special roles.
> I guess I could not use the special role for my datareaders/writers but
> the
> advantage of these roles is that I don't have to continuously go and grant
> permissions. If this is my only option in this scenario, then I guess I
> will
> have to go with it - I was just hoping there was something more elegant.
> Thanks in advance
>|||Thanks Dan,
I made a freshman mistake and when I thought the 'sa' couldn't access I was
in the wrong window.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:ez9IfD9tFHA.2592@.TK2MSFTNGP09.phx.gbl...
> Permissions are cumulative and DENY takes precedence. REVOKE removes
GRANT
> and DENY permissions previously assigned. Because all users are members
of
> public, you should DENY permissions to public only when you want to
prohibit
> access by all database users.
> 'sa' is a member of the sysadmin fixed server role and permissions are not
> checked for sysadmin role members. I believe you are mistaken when you
say
> that you prevented 'sa' from accessing the table.
> One method to allow Apps users to SELECT from the table and prevent other
> 'db_datareader' and 'db_datawriter' access:
> 1) GRANT SELECT ON YourTable TO Apps
> 2) DENY INSERT, UPDATE, DELETE ON YourTable TO Apps
> 3) create a new role to prevent access (e.g. 'DenyCertainTables')
> 4) DENY ALL ON YourTable TO DenyCertainTables
> 5) add all 'db_datareader' and 'db_datawriter' role members, except Apps
> role members, to the 'DenyCertainTables' role.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
> news:uqHH2W8tFHA.3528@.TK2MSFTNGP15.phx.gbl...
column[vbcol=seagreen]
database[vbcol=seagreen]
can[vbcol=seagreen]
to[vbcol=seagreen]
grant[vbcol=seagreen]
>sql

Grant select permission on all Tables to a DB role

Hello,
I got Win2k Advanced Server with SQL 2K running.
I am trying to figure out one single query will give a permission to all
tables under a database. A database has 20 - 30 tables with several custom
roles and it is pain to run a query that give a permission on one table at a
time.
Grant select on my_table to my_role
Grant update on my_table1 to my_role1
Can any one turn a light for me on this'
Thanks in advance.
SangHunThere might be another way, but you could use the sp_msforeachtable sproc.
Please note that it's undocumented/unsupported, so insert usual warnings
here (don't rely on it in production code or expect it to be there in the
next release, etc, etc.)
But I digress...
sp_msforeachtable 'grant select on ? to my_role'
Will grant select on every table in the current database to my_role.
Here is an article with more info on that sproc and another related sproc,
sp_msforeachdb:
http://www.dbazine.com/larsen5.shtml
"SangHunJung" <SangHunJung@.discussions.microsoft.com> wrote in message
news:CF69101B-4AE0-4C38-9466-FDCFE5818569@.microsoft.com...
> Hello,
> I got Win2k Advanced Server with SQL 2K running.
> I am trying to figure out one single query will give a permission to all
> tables under a database. A database has 20 - 30 tables with several
custom
> roles and it is pain to run a query that give a permission on one table at
a
> time.
> Grant select on my_table to my_role
> Grant update on my_table1 to my_role1
> Can any one turn a light for me on this'
> Thanks in advance.
> SangHun|||Why can't you just add the role to db_datareader and db_datawriter?
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eZvZS68eEHA.3100@.TK2MSFTNGP10.phx.gbl...
There might be another way, but you could use the sp_msforeachtable sproc.
Please note that it's undocumented/unsupported, so insert usual warnings
here (don't rely on it in production code or expect it to be there in the
next release, etc, etc.)
But I digress...
sp_msforeachtable 'grant select on ? to my_role'
Will grant select on every table in the current database to my_role.
Here is an article with more info on that sproc and another related sproc,
sp_msforeachdb:
http://www.dbazine.com/larsen5.shtml
"SangHunJung" <SangHunJung@.discussions.microsoft.com> wrote in message
news:CF69101B-4AE0-4C38-9466-FDCFE5818569@.microsoft.com...
> Hello,
> I got Win2k Advanced Server with SQL 2K running.
> I am trying to figure out one single query will give a permission to all
> tables under a database. A database has 20 - 30 tables with several
custom
> roles and it is pain to run a query that give a permission on one table at
a
> time.
> Grant select on my_table to my_role
> Grant update on my_table1 to my_role1
> Can any one turn a light for me on this'
> Thanks in advance.
> SangHun|||Does db_reader role has select permission on all existing tables?
I have seem that even I added a user to the db_reader role, the user didn't
have select permission to any table until I grant permission to specific
table for the user.
I thought I might create a read-only role and manage from there.
Thanks,
"Tom Moreau" wrote:

> Why can't you just add the role to db_datareader and db_datawriter?
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:eZvZS68eEHA.3100@.TK2MSFTNGP10.phx.gbl...
> There might be another way, but you could use the sp_msforeachtable sproc.
> Please note that it's undocumented/unsupported, so insert usual warnings
> here (don't rely on it in production code or expect it to be there in the
> next release, etc, etc.)
> But I digress...
> sp_msforeachtable 'grant select on ? to my_role'
> Will grant select on every table in the current database to my_role.
> Here is an article with more info on that sproc and another related sproc,
> sp_msforeachdb:
> http://www.dbazine.com/larsen5.shtml
>
> "SangHunJung" <SangHunJung@.discussions.microsoft.com> wrote in message
> news:CF69101B-4AE0-4C38-9466-FDCFE5818569@.microsoft.com...
> custom
> a
>
>|||Thanks Addm,
I will try that out on testing env.
SangHun
"Adam Machanic" wrote:

> There might be another way, but you could use the sp_msforeachtable sproc.
> Please note that it's undocumented/unsupported, so insert usual warnings
> here (don't rely on it in production code or expect it to be there in the
> next release, etc, etc.)
> But I digress...
> sp_msforeachtable 'grant select on ? to my_role'
> Will grant select on every table in the current database to my_role.
> Here is an article with more info on that sproc and another related sproc,
> sp_msforeachdb:
> http://www.dbazine.com/larsen5.shtml
>
> "SangHunJung" <SangHunJung@.discussions.microsoft.com> wrote in message
> news:CF69101B-4AE0-4C38-9466-FDCFE5818569@.microsoft.com...
> custom
> a
>
>|||The db_reader role has SELECT permission on all tables and views. I've not
seen the problem you describe. Perhaps you can give us a repro script?
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"SangHunJung" <SangHunJung@.discussions.microsoft.com> wrote in message
news:3BF1E1D9-412B-4282-B04F-DCF952713E36@.microsoft.com...
Does db_reader role has select permission on all existing tables?
I have seem that even I added a user to the db_reader role, the user didn't
have select permission to any table until I grant permission to specific
table for the user.
I thought I might create a read-only role and manage from there.
Thanks,
"Tom Moreau" wrote:

> Why can't you just add the role to db_datareader and db_datawriter?
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:eZvZS68eEHA.3100@.TK2MSFTNGP10.phx.gbl...
> There might be another way, but you could use the sp_msforeachtable sproc.
> Please note that it's undocumented/unsupported, so insert usual warnings
> here (don't rely on it in production code or expect it to be there in the
> next release, etc, etc.)
> But I digress...
> sp_msforeachtable 'grant select on ? to my_role'
> Will grant select on every table in the current database to my_role.
> Here is an article with more info on that sproc and another related sproc,
> sp_msforeachdb:
> http://www.dbazine.com/larsen5.shtml
>
> "SangHunJung" <SangHunJung@.discussions.microsoft.com> wrote in message
> news:CF69101B-4AE0-4C38-9466-FDCFE5818569@.microsoft.com...
> custom
at[vbcol=seagreen]
> a
>
>

Grant access to a single table

So here's the situation: I'm creating a front-end application to an SQL Server 2000 database and I need to create a new login to the database for the app.

Is it possible (or even; is it a good idea...) to create a logon that only has access to a single table?

Actually, I know it's possible - but I don't want to have to go through the hundreds of tables and deny access to them all! So I guess the question is;

Is there a quick and easy way of granting a login permissions to a single table only?Aha! I think I might have cracked it...

Create a new database role and then a new login who can have that role :)

However, the question of "should I do this" still stands.|||Why not?

I don't understand what your concern is.

For a single logon, you could even grant the access directly without bothering with a role.|||Ooh, how do I do that?|||The same way you grant access to a role.

Remember, though, that every login is a member of the PUBLIC role, so you will need to make sure that PUBLIC does NOT have access to any of the objects in the database.|||How did I miss that button before?! Woods and trees, eh.

The public role only has access to sys tables and dt* SPs; I don't have a problem with that.

I just figured this was the easiest and most effective way of implementing security on the FE. This is definately a route I will use again in the future :)

Thanks Blinddudesql

grant (Alter table) permission on a single table in the database

Product: Microsoft SQL server Enterprise Edition (2000)
O/S: MS Windoes NT (5.2)
Version: 8.00.760
===================================================== Can we grant (Alter table) permission on a single table on a database?
( I need to create a new user in a database of about 100 tables. This User
needs modify(Change structure of table) permission on a single table. User
want to incorporate triggers into the table. Can someone suggest me the best
way to grant permission for this.
Thanks
--
ontario, canada
=====================================================db
If you make him to be owner of the table. Such as CREATE TABLE user.T(c
INT...)"db" <db@.discussions.microsoft.com> wrote in message
news:0C3AA563-11A3-42FA-989D-6330159005A3@.microsoft.com...
> Product: Microsoft SQL server Enterprise Edition (2000)
> O/S: MS Windoes NT (5.2)
> Version: 8.00.760
> =====================================================> Can we grant (Alter table) permission on a single table on a database?
> ( I need to create a new user in a database of about 100 tables. This User
> needs modify(Change structure of table) permission on a single table. User
> want to incorporate triggers into the table. Can someone suggest me the
> best
> way to grant permission for this.
> Thanks
> --
> ontario, canada
> =====================================================

Good Day SQL Guru's

I have a rather lenghty question and I will try and be specific as possible.
I currently have a single server with Windows 2003 Enterprise running a
single instance of SQL Server Standard 2005. The Master and other "default"
databases are in the Default location on the C drive. The "Production"
databases are on "shared" resources on a CX-300 which is attached to my
Server via EMULEX HBA's. Now a Caviat to this configuration is that it used
to be part of a cluster.
When I first started working here I found that it had been configured with
an 8 gig C drive partion and I needed to reconfigure them one at a time, So
when I went to remove SQL off of the second node to be able to "evict" it
from the cluster, I did it through "Add/Remove Programs" and the set up came
up as normal, but after I selected the node and hit 'uninstall' It never
asked me the usual SQL cluster questions and Just uninstalled SQL, removing
all the shared "SQL Group" resources in the cluster" Not a tragedy, because I
still have a node, But very disconcerting and curious
So I rebuilt it and added it back to the cluster and manually configured
everything. It worked pretty good, Everything failed over (SQL services,
MSDTC, Drives and the databases attached okay, Howvere they since the Master
did not match the other, none of the "objects" were there. so what I did was
"stopped the services" on the good node, copied the databases into the
default folder on the new node and brought it up, it seemed to work. But when
I tried to attach my "production databases" it came back and said "recovery
in progress" and would not attach them, it didn't go for very long before My
SQL DB started whining about wanting to revert back because he had to go home.
SO, I am assuming that there was a problem doing it that way or maybe
eventually they would have recovered, We did try a "restore" first, But when
we did a full backup of the Good Node "master" and tried to restore it to the
new node, the versions didin't match.
Now, I have a few questions I was hoping I could get a few pointers on. I
have built two servers that I am going to build a new cluster on, so I want
to "migrate" these SQL "default" database to the new cluster, Since it is
also SAN attached, I am simply going to pull the single node out of the
"Storage Group" that has the LUNS for the SQL Resources and add the two SQL
Cluster NODES, giving the drives the exact same letters (of course attaching
them one node at a time)
So assuming I want to migrate the SQL default database to the Cluster is
there a migration tool that will do this relatively risk free and can I put
this database on a shared drive and have both nodes point to it and when
failover occurs, the node being failed from detaches and the node being
failed to attaches to these databases. The reason I ask is I have built
several SQL Clusters, But they have all been new ones that were going to be
used, I have not built one to replace a single node system.
I haven't done a lot of research myself yet, I am looking for any microsoft
tools that will assist me, But if anyone has done this or has some sagely
advise, it would be greatly appreciated
--
Scott A Cummins
Sr. Systems Engineer
Equity Analytics
( A division of Merrill Lynch)
14614 N. Keirland Blvd
Scottsdale, AZ 85254
480-998-3515I would treat this as a side-by-side migration. I would build a new cluster
and copy the data files to the new system. You can try a detach-move
LUNS-Reattach from the stand-alone machine to the cluster, but there is no
way I would try and migrate the system databases. Treat this as a normal
SQL host migration and you might come out of it OK.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Scott Cummins" <scummins@.equitymethods.com> wrote in message
news:EF1A1CB2-8192-41C7-8088-4C2B1EC78835@.microsoft.com...
>I have a rather lenghty question and I will try and be specific as
>possible.
> I currently have a single server with Windows 2003 Enterprise running a
> single instance of SQL Server Standard 2005. The Master and other
> "default"
> databases are in the Default location on the C drive. The "Production"
> databases are on "shared" resources on a CX-300 which is attached to my
> Server via EMULEX HBA's. Now a Caviat to this configuration is that it
> used
> to be part of a cluster.
> When I first started working here I found that it had been configured with
> an 8 gig C drive partion and I needed to reconfigure them one at a time,
> So
> when I went to remove SQL off of the second node to be able to "evict" it
> from the cluster, I did it through "Add/Remove Programs" and the set up
> came
> up as normal, but after I selected the node and hit 'uninstall' It never
> asked me the usual SQL cluster questions and Just uninstalled SQL,
> removing
> all the shared "SQL Group" resources in the cluster" Not a tragedy,
> because I
> still have a node, But very disconcerting and curious
> So I rebuilt it and added it back to the cluster and manually configured
> everything. It worked pretty good, Everything failed over (SQL services,
> MSDTC, Drives and the databases attached okay, Howvere they since the
> Master
> did not match the other, none of the "objects" were there. so what I did
> was
> "stopped the services" on the good node, copied the databases into the
> default folder on the new node and brought it up, it seemed to work. But
> when
> I tried to attach my "production databases" it came back and said
> "recovery
> in progress" and would not attach them, it didn't go for very long before
> My
> SQL DB started whining about wanting to revert back because he had to go
> home.
> SO, I am assuming that there was a problem doing it that way or maybe
> eventually they would have recovered, We did try a "restore" first, But
> when
> we did a full backup of the Good Node "master" and tried to restore it to
> the
> new node, the versions didin't match.
> Now, I have a few questions I was hoping I could get a few pointers on. I
> have built two servers that I am going to build a new cluster on, so I
> want
> to "migrate" these SQL "default" database to the new cluster, Since it is
> also SAN attached, I am simply going to pull the single node out of the
> "Storage Group" that has the LUNS for the SQL Resources and add the two
> SQL
> Cluster NODES, giving the drives the exact same letters (of course
> attaching
> them one node at a time)
> So assuming I want to migrate the SQL default database to the Cluster is
> there a migration tool that will do this relatively risk free and can I
> put
> this database on a shared drive and have both nodes point to it and when
> failover occurs, the node being failed from detaches and the node being
> failed to attaches to these databases. The reason I ask is I have built
> several SQL Clusters, But they have all been new ones that were going to
> be
> used, I have not built one to replace a single node system.
> I haven't done a lot of research myself yet, I am looking for any
> microsoft
> tools that will assist me, But if anyone has done this or has some sagely
> advise, it would be greatly appreciated
> --
> Scott A Cummins
> Sr. Systems Engineer
> Equity Analytics
> ( A division of Merrill Lynch)
> 14614 N. Keirland Blvd
> Scottsdale, AZ 85254
> 480-998-3515|||Hey Geoff
Okay, I will..Now What is a "Normal Host Migration" and is it possible to
create a single set of system databases(Master, Model, MSDB and Tempdb) on a
shared drive and have both nodes connect to it when they fail over
Just so you know, I am by no stretch of the imagination a SQL guy, My DB is
even having issues
--
Scott A Cummins
Sr. Systems Engineer
Equity Analytics
( A division of Merrill Lynch)
14614 N. Keirland Blvd
Scottsdale, AZ 85254
480-998-3515
"Scott Cummins" wrote:
> I have a rather lenghty question and I will try and be specific as possible.
> I currently have a single server with Windows 2003 Enterprise running a
> single instance of SQL Server Standard 2005. The Master and other "default"
> databases are in the Default location on the C drive. The "Production"
> databases are on "shared" resources on a CX-300 which is attached to my
> Server via EMULEX HBA's. Now a Caviat to this configuration is that it used
> to be part of a cluster.
> When I first started working here I found that it had been configured with
> an 8 gig C drive partion and I needed to reconfigure them one at a time, So
> when I went to remove SQL off of the second node to be able to "evict" it
> from the cluster, I did it through "Add/Remove Programs" and the set up came
> up as normal, but after I selected the node and hit 'uninstall' It never
> asked me the usual SQL cluster questions and Just uninstalled SQL, removing
> all the shared "SQL Group" resources in the cluster" Not a tragedy, because I
> still have a node, But very disconcerting and curious
> So I rebuilt it and added it back to the cluster and manually configured
> everything. It worked pretty good, Everything failed over (SQL services,
> MSDTC, Drives and the databases attached okay, Howvere they since the Master
> did not match the other, none of the "objects" were there. so what I did was
> "stopped the services" on the good node, copied the databases into the
> default folder on the new node and brought it up, it seemed to work. But when
> I tried to attach my "production databases" it came back and said "recovery
> in progress" and would not attach them, it didn't go for very long before My
> SQL DB started whining about wanting to revert back because he had to go home.
> SO, I am assuming that there was a problem doing it that way or maybe
> eventually they would have recovered, We did try a "restore" first, But when
> we did a full backup of the Good Node "master" and tried to restore it to the
> new node, the versions didin't match.
> Now, I have a few questions I was hoping I could get a few pointers on. I
> have built two servers that I am going to build a new cluster on, so I want
> to "migrate" these SQL "default" database to the new cluster, Since it is
> also SAN attached, I am simply going to pull the single node out of the
> "Storage Group" that has the LUNS for the SQL Resources and add the two SQL
> Cluster NODES, giving the drives the exact same letters (of course attaching
> them one node at a time)
> So assuming I want to migrate the SQL default database to the Cluster is
> there a migration tool that will do this relatively risk free and can I put
> this database on a shared drive and have both nodes point to it and when
> failover occurs, the node being failed from detaches and the node being
> failed to attaches to these databases. The reason I ask is I have built
> several SQL Clusters, But they have all been new ones that were going to be
> used, I have not built one to replace a single node system.
> I haven't done a lot of research myself yet, I am looking for any microsoft
> tools that will assist me, But if anyone has done this or has some sagely
> advise, it would be greatly appreciated
> --
> Scott A Cummins
> Sr. Systems Engineer
> Equity Analytics
> ( A division of Merrill Lynch)
> 14614 N. Keirland Blvd
> Scottsdale, AZ 85254
> 480-998-3515|||What you describe is exactly how clustering is supposed to work. SQL has
binaries on each node, but all databases, including system databases are on
a "shared" storage system. The Cluster service arbitrates which node owns
the resources associated with a particular SQL server instance. The
service, network name, IP address, and all storage disks go together to a
new node when the group fails over or is moved.
So, set up the cluster (single node if necessary) script your logins, jobs,
etc. and get them moved to the new system. You can then move the database
files to the new cluster and attach them, or use a backup-restore technique
to sync the systems. That is the very short version of a normal migration.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Scott Cummins" <scummins@.equitymethods.com> wrote in message
news:A0FC13A4-857B-4B08-9DDC-BD93F32504A7@.microsoft.com...
> Hey Geoff
> Okay, I will..Now What is a "Normal Host Migration" and is it possible to
> create a single set of system databases(Master, Model, MSDB and Tempdb) on
> a
> shared drive and have both nodes connect to it when they fail over
> Just so you know, I am by no stretch of the imagination a SQL guy, My DB
> is
> even having issues
> --
> Scott A Cummins
> Sr. Systems Engineer
> Equity Analytics
> ( A division of Merrill Lynch)
> 14614 N. Keirland Blvd
> Scottsdale, AZ 85254
> 480-998-3515
>
> "Scott Cummins" wrote:
>> I have a rather lenghty question and I will try and be specific as
>> possible.
>> I currently have a single server with Windows 2003 Enterprise running a
>> single instance of SQL Server Standard 2005. The Master and other
>> "default"
>> databases are in the Default location on the C drive. The "Production"
>> databases are on "shared" resources on a CX-300 which is attached to my
>> Server via EMULEX HBA's. Now a Caviat to this configuration is that it
>> used
>> to be part of a cluster.
>> When I first started working here I found that it had been configured
>> with
>> an 8 gig C drive partion and I needed to reconfigure them one at a time,
>> So
>> when I went to remove SQL off of the second node to be able to "evict" it
>> from the cluster, I did it through "Add/Remove Programs" and the set up
>> came
>> up as normal, but after I selected the node and hit 'uninstall' It never
>> asked me the usual SQL cluster questions and Just uninstalled SQL,
>> removing
>> all the shared "SQL Group" resources in the cluster" Not a tragedy,
>> because I
>> still have a node, But very disconcerting and curious
>> So I rebuilt it and added it back to the cluster and manually configured
>> everything. It worked pretty good, Everything failed over (SQL services,
>> MSDTC, Drives and the databases attached okay, Howvere they since the
>> Master
>> did not match the other, none of the "objects" were there. so what I did
>> was
>> "stopped the services" on the good node, copied the databases into the
>> default folder on the new node and brought it up, it seemed to work. But
>> when
>> I tried to attach my "production databases" it came back and said
>> "recovery
>> in progress" and would not attach them, it didn't go for very long before
>> My
>> SQL DB started whining about wanting to revert back because he had to go
>> home.
>> SO, I am assuming that there was a problem doing it that way or maybe
>> eventually they would have recovered, We did try a "restore" first, But
>> when
>> we did a full backup of the Good Node "master" and tried to restore it to
>> the
>> new node, the versions didin't match.
>> Now, I have a few questions I was hoping I could get a few pointers on. I
>> have built two servers that I am going to build a new cluster on, so I
>> want
>> to "migrate" these SQL "default" database to the new cluster, Since it is
>> also SAN attached, I am simply going to pull the single node out of the
>> "Storage Group" that has the LUNS for the SQL Resources and add the two
>> SQL
>> Cluster NODES, giving the drives the exact same letters (of course
>> attaching
>> them one node at a time)
>> So assuming I want to migrate the SQL default database to the Cluster is
>> there a migration tool that will do this relatively risk free and can I
>> put
>> this database on a shared drive and have both nodes point to it and when
>> failover occurs, the node being failed from detaches and the node being
>> failed to attaches to these databases. The reason I ask is I have built
>> several SQL Clusters, But they have all been new ones that were going to
>> be
>> used, I have not built one to replace a single node system.
>> I haven't done a lot of research myself yet, I am looking for any
>> microsoft
>> tools that will assist me, But if anyone has done this or has some sagely
>> advise, it would be greatly appreciated
>> --
>> Scott A Cummins
>> Sr. Systems Engineer
>> Equity Analytics
>> ( A division of Merrill Lynch)
>> 14614 N. Keirland Blvd
>> Scottsdale, AZ 85254
>> 480-998-3515|||Geoff
Thanks again, that is a very good piece of Knowledge to have, For some
reason I had assumed that the system databases were loaded separately on each
node and because it was cluster "aware" it replictaed any changes on one
node to the other node so that the "system databases" were exactly the same.
It seems my DBA has so much info in the system databases, he will probably
have to script everything over. He was hoping just to be bale to "migrate" or
"copy" the current System databases over to the new system.
Again, you are of tremendous help and I really appreciate it
--
Scott A Cummins
Sr. Systems Engineer
Equity Analytics
( A division of Merrill Lynch)
14614 N. Keirland Blvd
Scottsdale, AZ 85254
480-998-3515
"Geoff N. Hiten" wrote:
> What you describe is exactly how clustering is supposed to work. SQL has
> binaries on each node, but all databases, including system databases are on
> a "shared" storage system. The Cluster service arbitrates which node owns
> the resources associated with a particular SQL server instance. The
> service, network name, IP address, and all storage disks go together to a
> new node when the group fails over or is moved.
> So, set up the cluster (single node if necessary) script your logins, jobs,
> etc. and get them moved to the new system. You can then move the database
> files to the new cluster and attach them, or use a backup-restore technique
> to sync the systems. That is the very short version of a normal migration.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "Scott Cummins" <scummins@.equitymethods.com> wrote in message
> news:A0FC13A4-857B-4B08-9DDC-BD93F32504A7@.microsoft.com...
> > Hey Geoff
> >
> > Okay, I will..Now What is a "Normal Host Migration" and is it possible to
> > create a single set of system databases(Master, Model, MSDB and Tempdb) on
> > a
> > shared drive and have both nodes connect to it when they fail over
> >
> > Just so you know, I am by no stretch of the imagination a SQL guy, My DB
> > is
> > even having issues
> > --
> > Scott A Cummins
> > Sr. Systems Engineer
> > Equity Analytics
> > ( A division of Merrill Lynch)
> > 14614 N. Keirland Blvd
> > Scottsdale, AZ 85254
> > 480-998-3515
> >
> >
> > "Scott Cummins" wrote:
> >
> >> I have a rather lenghty question and I will try and be specific as
> >> possible.
> >>
> >> I currently have a single server with Windows 2003 Enterprise running a
> >> single instance of SQL Server Standard 2005. The Master and other
> >> "default"
> >> databases are in the Default location on the C drive. The "Production"
> >> databases are on "shared" resources on a CX-300 which is attached to my
> >> Server via EMULEX HBA's. Now a Caviat to this configuration is that it
> >> used
> >> to be part of a cluster.
> >>
> >> When I first started working here I found that it had been configured
> >> with
> >> an 8 gig C drive partion and I needed to reconfigure them one at a time,
> >> So
> >> when I went to remove SQL off of the second node to be able to "evict" it
> >> from the cluster, I did it through "Add/Remove Programs" and the set up
> >> came
> >> up as normal, but after I selected the node and hit 'uninstall' It never
> >> asked me the usual SQL cluster questions and Just uninstalled SQL,
> >> removing
> >> all the shared "SQL Group" resources in the cluster" Not a tragedy,
> >> because I
> >> still have a node, But very disconcerting and curious
> >>
> >> So I rebuilt it and added it back to the cluster and manually configured
> >> everything. It worked pretty good, Everything failed over (SQL services,
> >> MSDTC, Drives and the databases attached okay, Howvere they since the
> >> Master
> >> did not match the other, none of the "objects" were there. so what I did
> >> was
> >> "stopped the services" on the good node, copied the databases into the
> >> default folder on the new node and brought it up, it seemed to work. But
> >> when
> >> I tried to attach my "production databases" it came back and said
> >> "recovery
> >> in progress" and would not attach them, it didn't go for very long before
> >> My
> >> SQL DB started whining about wanting to revert back because he had to go
> >> home.
> >>
> >> SO, I am assuming that there was a problem doing it that way or maybe
> >> eventually they would have recovered, We did try a "restore" first, But
> >> when
> >> we did a full backup of the Good Node "master" and tried to restore it to
> >> the
> >> new node, the versions didin't match.
> >>
> >> Now, I have a few questions I was hoping I could get a few pointers on. I
> >> have built two servers that I am going to build a new cluster on, so I
> >> want
> >> to "migrate" these SQL "default" database to the new cluster, Since it is
> >> also SAN attached, I am simply going to pull the single node out of the
> >> "Storage Group" that has the LUNS for the SQL Resources and add the two
> >> SQL
> >> Cluster NODES, giving the drives the exact same letters (of course
> >> attaching
> >> them one node at a time)
> >>
> >> So assuming I want to migrate the SQL default database to the Cluster is
> >> there a migration tool that will do this relatively risk free and can I
> >> put
> >> this database on a shared drive and have both nodes point to it and when
> >> failover occurs, the node being failed from detaches and the node being
> >> failed to attaches to these databases. The reason I ask is I have built
> >> several SQL Clusters, But they have all been new ones that were going to
> >> be
> >> used, I have not built one to replace a single node system.
> >>
> >> I haven't done a lot of research myself yet, I am looking for any
> >> microsoft
> >> tools that will assist me, But if anyone has done this or has some sagely
> >> advise, it would be greatly appreciated
> >> --
> >> Scott A Cummins
> >> Sr. Systems Engineer
> >> Equity Analytics
> >> ( A division of Merrill Lynch)
> >> 14614 N. Keirland Blvd
> >> Scottsdale, AZ 85254
> >> 480-998-3515
>

Good and small toolbox for MS-SQL?

Hi:
I'm looking for an app that does not require installation (single exe type
of app) that I can use for querying, editing (via a grid) and exporting data
from MSSQL, does anyone know of such a tool?
The reason I don't want to install a multiple dll app is so I can carry the
app around on CD to my clients and use it on their machines and take it away
with me when I finish.
TIA,
Martin.
Hi
Is this not called a laptop?
You may want to check out the products on
http://www.aspfaq.com/show.asp?id=2442
John
"Martin Hart - Memory Soft, S.L." <memorysoftsl _at_ infotelecom _dot_ es>
wrote in message news:O3OHqjUVEHA.584@.TK2MSFTNGP09.phx.gbl...
> Hi:
> I'm looking for an app that does not require installation (single exe type
> of app) that I can use for querying, editing (via a grid) and exporting
data
> from MSSQL, does anyone know of such a tool?
> The reason I don't want to install a multiple dll app is so I can carry
the
> app around on CD to my clients and use it on their machines and take it
away
> with me when I finish.
> TIA,
> Martin.
>
|||I guess SQLExecMS from www.laplas-soft.com is what you are looking for.
JS
"Martin Hart - Memory Soft, S.L." <memorysoftsl _at_ infotelecom _dot_ es>
wrote in message news:O3OHqjUVEHA.584@.TK2MSFTNGP09.phx.gbl...
> Hi:
> I'm looking for an app that does not require installation (single exe type
> of app) that I can use for querying, editing (via a grid) and exporting
data
> from MSSQL, does anyone know of such a tool?
> The reason I don't want to install a multiple dll app is so I can carry
the
> app around on CD to my clients and use it on their machines and take it
away
> with me when I finish.
> TIA,
> Martin.
>
|||Give our free tool called Query Analyzer Tool a spin. It currently comes in
an installer simply because we need people to read the license agreement.
But you can just install it, then copy only the exe to another workstation
and it will run just fine, though you might also want to copy the help file
to understand its many features.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"Martin Hart - Memory Soft, S.L." <memorysoftsl _at_ infotelecom _dot_ es>
wrote in message news:O3OHqjUVEHA.584@.TK2MSFTNGP09.phx.gbl...
> Hi:
> I'm looking for an app that does not require installation (single exe type
> of app) that I can use for querying, editing (via a grid) and exporting
data
> from MSSQL, does anyone know of such a tool?
> The reason I don't want to install a multiple dll app is so I can carry
the
> app around on CD to my clients and use it on their machines and take it
away
> with me when I finish.
> TIA,
> Martin.
>
|||>I'm looking for an app that does not require installation (single exe type
>of app) that I can use for querying, editing (via a grid) and exporting data
>from MSSQL, does anyone know of such a tool?
I'd have a look at Toad for SQL Server by Quest - freeware.
www.toadsoft.com
Marc
================================================== ==============
Marc Scheuner May The Source Be With You!
Bern, Switzerland m.scheuner(at)inova.ch
|||Thanks for all the suggestions, I'll check them out.
I have also seen EMS MS SQL Manager from EMS-Hi-tech, look pretty good.
Regards,
Martin.
"Martin Hart - Memory Soft, S.L." <memorysoftsl _at_ infotelecom _dot_ es>
escribi en el mensaje news:O3OHqjUVEHA.584@.TK2MSFTNGP09.phx.gbl...
> Hi:
> I'm looking for an app that does not require installation (single exe type
> of app) that I can use for querying, editing (via a grid) and exporting
data
> from MSSQL, does anyone know of such a tool?
> The reason I don't want to install a multiple dll app is so I can carry
the
> app around on CD to my clients and use it on their machines and take it
away
> with me when I finish.
> TIA,
> Martin.
>
|||Martin Hart - Memory Soft, S.L. wrote:
> Hi:
> I'm looking for an app that does not require installation (single exe
> type of app) that I can use for querying, editing (via a grid) and
> exporting data from MSSQL, does anyone know of such a tool?
OmniView is made for this
www.krell-software.com
Steve Troxell

global dataset for n number of forms

hi friends ,

Is there anyway to share a single dataset for different forms.. coz i am repeatedly using the same query for different forms...

No, you cannot share a dataset among reports. Anyway, it doesnt make a big difference because the same query is going to run against the database even it is same for different reports.

If you are using SQL query text in your reports, consider creating a stored procedure for the query and use the stored procedure in all your reports.

Shyam

|||thank you shyam.......|||

Can you please mark the post as answer?

global constant for multiple reports

I have a set of 25 reports that contain the links to the other application
(URL). Is there a way to define a single "global" string for all the reports
to quickly replace that url with the right one? Let's say it's
http://testingserver/app for testing, and http://production/app for
production. I hate to change the link value on every report. Using custom
assemblies to keep such common references looks like too much overhead. Are
there any other global report configuration options I'm missing?
--
Regards,
Dmitry DuginovHello Dmitry,
You could do this in the VS.NET 2005 IDE.
Click the Edit -> Find and Replace -> Replace in Files.
Type the http://testingserver/app in the Find what section, and type
http://production/app in the Replace with section.
Then, click the Find options and type *.rdl in the Look at these file types.
And then, you could click replace all button and all the files in your
solutionn will be modified.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Sorry, but this "approach" just doesn't sound right. It's
"hardcode-change-redeploy". How it's better than "hardcode-change-recomplie"
for regular applications? It's not; that's why we are always trying to keep
some variables in config files: to change them, not the source code...
D.
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:zH9Hlb%239GHA.4432@.TK2MSFTNGXA01.phx.gbl...
> You could do this in the VS.NET 2005 IDE.
> Click the Edit -> Find and Replace -> Replace in Files.
> Type the http://testingserver/app in the Find what section, and type
> http://production/app in the Replace with section.
> Then, click the Find options and type *.rdl in the Look at these file
types.
> And then, you could click replace all button and all the files in your
> solutionn will be modified.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
>
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello Dmitry,
You could not do this in the Reporting Services Designer.
Since the RDL file is a XML format file, you could modify the xml file
before you deploy to the report server.
This is the only approach you could do in the reporting services.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.