Permissions set to Roles disappeared

We setup a number of roles with access rights to tables in the DB. This week for some unknown reason, rights on these roles disappeared.

We had to run a restore to reset the roles in the database. After the restore, we could not reproduce the problem.

Are there scenarios to avoid that would cause rights to drop from roles and users? (These rights were gone not just hidden)

Tim.

Other than someone dropping the roles and then recreating them, I don't see an accidental way for this to happen. Dropping a role would drop all permissions associated with the role.

Thanks

Laurentiu

|||

This happened to me today. Last week I'd setup specific permissions limiting a SQL server account to specific tables/procedures in tempdb. The account is used for maintaining an asp.net application's state. The permissions set are below. Today those permissions were gone. Any idea why?

use tempdb;

go

sp_grantdbaccess MyPeakASPState;

GRANT SELECT on ASPStateTempApplications to MyPeakASPState;

GRANT INSERT on ASPStateTempApplications to MyPeakASPState;

GRANT SELECT on ASPStateTempSessions to MyPeakASPState;

GRANT INSERT on ASPStateTempSessions to MyPeakASPState;

GRANT UPDATE on ASPStateTempSessions to MyPeakASPState;

GO

use aspstate

go

GRANT EXEC ON TempGetStateItem TO MyPeakASPState;

GO

GRANT EXEC ON TempGetStateItem2 TO MyPeakASPState;

GO

GRANT EXEC ON TempGetStateItemExclusive TO MyPeakASPState;

GO

GRANT EXEC ON TempGetStateItemExclusive2 TO MyPeakASPState;

GO

GRANT EXEC ON TempReleaseStateItemExclusive TO MyPeakASPState;

GO

GRANT EXEC ON TempInsertStateItemShort TO MyPeakASPState;

GO

GRANT EXEC ON TempInsertStateItemLong TO MyPeakASPState;

GO

GRANT EXEC ON TempUpdateStateItemShort TO MyPeakASPState;

GO

GRANT EXEC ON TempUpdateStateItemShortNullLong TO MyPeakASPState;

GO

GRANT EXEC ON TempUpdateStateItemLong TO MyPeakASPState;

GO

GRANT EXEC ON TempUpdateStateItemLongNullShort TO MyPeakASPState;

GO

GRANT EXEC ON TempRemoveStateItem TO MyPeakASPState;

GO

GRANT EXEC ON TempResetTimeout TO MyPeakASPState;

GO

GRANT EXEC ON DeleteExpiredSessions TO MyPeakASPState;

GO

GRANT EXEC ON DropTempTables TO MyPeakASPState;

GO

GRANT EXEC ON GetMajorVersion TO MyPeakASPState;

GO

GRANT EXEC ON CreateTempTables TO MyPeakASPState;

GO

GRANT EXEC ON ResetData TO MyPeakASPState;

GO

GRANT EXEC ON TempGetAppID TO MyPeakASPState

|||

I wish I could help you. I'm interested that it happened to someone else.

The only advice I can give you - becareful not to change logins when changing security.

My problem may have occurred because I was testing security on a user.

Tim.

|||

I think I know what may be happening, please correct me if my assumption is incorrect. The privileges that get lost are the ones related to tempdb, correct?

use tempdb;

go

sp_grantdbaccess MyPeakASPState;

GRANT SELECT on ASPStateTempApplications to MyPeakASPState;

GRANT INSERT on ASPStateTempApplications to MyPeakASPState;

GRANT SELECT on ASPStateTempSessions to MyPeakASPState;

GRANT INSERT on ASPStateTempSessions to MyPeakASPState;

GRANT UPDATE on ASPStateTempSessions to MyPeakASPState;

GO

Tempdb is recreated every time the server is restarted, therefore any information stored there should be consider volatile. Every time SQL Server is restarted all of the permissions listed above will be lost.

I hope this information helps,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks, I didn't know that.

Permissions set to Roles disappeared

We setup a number of roles with access rights to tables in the DB. This week for some unknown reason, rights on these roles disappeared.

We had to run a restore to reset the roles in the database. After the restore, we could not reproduce the problem.

Are there scenarios to avoid that would cause rights to drop from roles and users? (These rights were gone not just hidden)

Tim.

Other than someone dropping the roles and then recreating them, I don't see an accidental way for this to happen. Dropping a role would drop all permissions associated with the role.

Thanks

Laurentiu

|||

This happened to me today. Last week I'd setup specific permissions limiting a SQL server account to specific tables/procedures in tempdb. The account is used for maintaining an asp.net application's state. The permissions set are below. Today those permissions were gone. Any idea why?

use tempdb;

go

sp_grantdbaccess MyPeakASPState;

GRANT SELECT on ASPStateTempApplications to MyPeakASPState;

GRANT INSERT on ASPStateTempApplications to MyPeakASPState;

GRANT SELECT on ASPStateTempSessions to MyPeakASPState;

GRANT INSERT on ASPStateTempSessions to MyPeakASPState;

GRANT UPDATE on ASPStateTempSessions to MyPeakASPState;

GO

use aspstate

go

GRANT EXEC ON TempGetStateItem TO MyPeakASPState;

GO

GRANT EXEC ON TempGetStateItem2 TO MyPeakASPState;

GO

GRANT EXEC ON TempGetStateItemExclusive TO MyPeakASPState;

GO

GRANT EXEC ON TempGetStateItemExclusive2 TO MyPeakASPState;

GO

GRANT EXEC ON TempReleaseStateItemExclusive TO MyPeakASPState;

GO

GRANT EXEC ON TempInsertStateItemShort TO MyPeakASPState;

GO

GRANT EXEC ON TempInsertStateItemLong TO MyPeakASPState;

GO

GRANT EXEC ON TempUpdateStateItemShort TO MyPeakASPState;

GO

GRANT EXEC ON TempUpdateStateItemShortNullLong TO MyPeakASPState;

GO

GRANT EXEC ON TempUpdateStateItemLong TO MyPeakASPState;

GO

GRANT EXEC ON TempUpdateStateItemLongNullShort TO MyPeakASPState;

GO

GRANT EXEC ON TempRemoveStateItem TO MyPeakASPState;

GO

GRANT EXEC ON TempResetTimeout TO MyPeakASPState;

GO

GRANT EXEC ON DeleteExpiredSessions TO MyPeakASPState;

GO

GRANT EXEC ON DropTempTables TO MyPeakASPState;

GO

GRANT EXEC ON GetMajorVersion TO MyPeakASPState;

GO

GRANT EXEC ON CreateTempTables TO MyPeakASPState;

GO

GRANT EXEC ON ResetData TO MyPeakASPState;

GO

GRANT EXEC ON TempGetAppID TO MyPeakASPState

|||

I wish I could help you. I'm interested that it happened to someone else.

The only advice I can give you - becareful not to change logins when changing security.

My problem may have occurred because I was testing security on a user.

Tim.

|||

I think I know what may be happening, please correct me if my assumption is incorrect. The privileges that get lost are the ones related to tempdb, correct?

use tempdb;

go

sp_grantdbaccess MyPeakASPState;

GRANT SELECT on ASPStateTempApplications to MyPeakASPState;

GRANT INSERT on ASPStateTempApplications to MyPeakASPState;

GRANT SELECT on ASPStateTempSessions to MyPeakASPState;

GRANT INSERT on ASPStateTempSessions to MyPeakASPState;

GRANT UPDATE on ASPStateTempSessions to MyPeakASPState;

GO

Tempdb is recreated every time the server is restarted, therefore any information stored there should be consider volatile. Every time SQL Server is restarted all of the permissions listed above will be lost.

I hope this information helps,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks, I didn't know that.

Permissions set to Roles disappeared

We setup a number of roles with access rights to tables in the DB. This week for some unknown reason, rights on these roles disappeared.

We had to run a restore to reset the roles in the database. After the restore, we could not reproduce the problem.

Are there scenarios to avoid that would cause rights to drop from roles and users? (These rights were gone not just hidden)

Tim.

Other than someone dropping the roles and then recreating them, I don't see an accidental way for this to happen. Dropping a role would drop all permissions associated with the role.

Thanks

Laurentiu

|||

This happened to me today. Last week I'd setup specific permissions limiting a SQL server account to specific tables/procedures in tempdb. The account is used for maintaining an asp.net application's state. The permissions set are below. Today those permissions were gone. Any idea why?

use tempdb;

go

sp_grantdbaccess MyPeakASPState;

GRANT SELECT on ASPStateTempApplications to MyPeakASPState;

GRANT INSERT on ASPStateTempApplications to MyPeakASPState;

GRANT SELECT on ASPStateTempSessions to MyPeakASPState;

GRANT INSERT on ASPStateTempSessions to MyPeakASPState;

GRANT UPDATE on ASPStateTempSessions to MyPeakASPState;

GO

use aspstate

go

GRANT EXEC ON TempGetStateItem TO MyPeakASPState;

GO

GRANT EXEC ON TempGetStateItem2 TO MyPeakASPState;

GO

GRANT EXEC ON TempGetStateItemExclusive TO MyPeakASPState;

GO

GRANT EXEC ON TempGetStateItemExclusive2 TO MyPeakASPState;

GO

GRANT EXEC ON TempReleaseStateItemExclusive TO MyPeakASPState;

GO

GRANT EXEC ON TempInsertStateItemShort TO MyPeakASPState;

GO

GRANT EXEC ON TempInsertStateItemLong TO MyPeakASPState;

GO

GRANT EXEC ON TempUpdateStateItemShort TO MyPeakASPState;

GO

GRANT EXEC ON TempUpdateStateItemShortNullLong TO MyPeakASPState;

GO

GRANT EXEC ON TempUpdateStateItemLong TO MyPeakASPState;

GO

GRANT EXEC ON TempUpdateStateItemLongNullShort TO MyPeakASPState;

GO

GRANT EXEC ON TempRemoveStateItem TO MyPeakASPState;

GO

GRANT EXEC ON TempResetTimeout TO MyPeakASPState;

GO

GRANT EXEC ON DeleteExpiredSessions TO MyPeakASPState;

GO

GRANT EXEC ON DropTempTables TO MyPeakASPState;

GO

GRANT EXEC ON GetMajorVersion TO MyPeakASPState;

GO

GRANT EXEC ON CreateTempTables TO MyPeakASPState;

GO

GRANT EXEC ON ResetData TO MyPeakASPState;

GO

GRANT EXEC ON TempGetAppID TO MyPeakASPState

|||

I wish I could help you. I'm interested that it happened to someone else.

The only advice I can give you - becareful not to change logins when changing security.

My problem may have occurred because I was testing security on a user.

Tim.

|||

I think I know what may be happening, please correct me if my assumption is incorrect. The privileges that get lost are the ones related to tempdb, correct?

use tempdb;

go

sp_grantdbaccess MyPeakASPState;

GRANT SELECT on ASPStateTempApplications to MyPeakASPState;

GRANT INSERT on ASPStateTempApplications to MyPeakASPState;

GRANT SELECT on ASPStateTempSessions to MyPeakASPState;

GRANT INSERT on ASPStateTempSessions to MyPeakASPState;

GRANT UPDATE on ASPStateTempSessions to MyPeakASPState;

GO

Tempdb is recreated every time the server is restarted, therefore any information stored there should be consider volatile. Every time SQL Server is restarted all of the permissions listed above will be lost.

I hope this information helps,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks, I didn't know that.

sql

permissions on a column

hi,

i run a sqlserver 2000 and im having problems setting a permission a
column in a table..

under a database i have a User that has dataread rights on each table
in the database, but in one table i want to prevent the user from
seeing a column in one perticular table.

i have created the user under security and then i choose the database
user - properties..and i set a X in the specified column...

when i log on as the datareader user i cant see any colummn at all in
the table..

what have a done wrong?

Steve[posted and mailed, vnligen svara i nys]

steve (stebo@.privat.utfors.se) writes:
> i run a sqlserver 2000 and im having problems setting a permission a
> column in a table..
> under a database i have a User that has dataread rights on each table
> in the database, but in one table i want to prevent the user from
> seeing a column in one perticular table.
> i have created the user under security and then i choose the database
> user - properties..and i set a X in the specified column...
> when i log on as the datareader user i cant see any colummn at all in
> the table..
> what have a done wrong?

Used a GUI instead of looking up the commands in Books Online. GUIs may
do the what you expect, or they may do something else. The command to
use is DENY. Here is an example:

use tempdb
go
exec sp_addlogin accesstest, secret
exec sp_adduser accesstest
exec sp_addrolemember db_datareader, accesstest
go
CREATE TABLE tbl (a int NOT NULL, b varchar(23) NOT NULL)
INSERT tbl (a, b) VALUES (9, 'Top secret')
go
SETUSER 'accesstest'
go
SELECT * FROM tbl
go
SETUSER
go
DENY ALL ON tbl (b) TO accesstest
go
SETUSER 'accesstest'
go
SELECT * FROM tbl
go
SETUSER
go
DROP TABLE tbl
EXEC sp_dropuser accesstest
EXEC sp_droplogin accesstest

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||thanks for your help!

I will try this, what do you think about using a VIEW, is this a good choice?

BR

Steve
--

Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns959DF3D2FD2D0Yazorman@.127.0.0.1>...
> [posted and mailed, vnligen svara i nys]
> steve (stebo@.privat.utfors.se) writes:
> > i run a sqlserver 2000 and im having problems setting a permission a
> > column in a table..
> > under a database i have a User that has dataread rights on each table
> > in the database, but in one table i want to prevent the user from
> > seeing a column in one perticular table.
> > i have created the user under security and then i choose the database
> > user - properties..and i set a X in the specified column...
> > when i log on as the datareader user i cant see any colummn at all in
> > the table..
> > what have a done wrong?
> Used a GUI instead of looking up the commands in Books Online. GUIs may
> do the what you expect, or they may do something else. The command to
> use is DENY. Here is an example:
> use tempdb
> go
> exec sp_addlogin accesstest, secret
> exec sp_adduser accesstest
> exec sp_addrolemember db_datareader, accesstest
> go
> CREATE TABLE tbl (a int NOT NULL, b varchar(23) NOT NULL)
> INSERT tbl (a, b) VALUES (9, 'Top secret')
> go
> SETUSER 'accesstest'
> go
> SELECT * FROM tbl
> go
> SETUSER
> go
> DENY ALL ON tbl (b) TO accesstest
> go
> SETUSER 'accesstest'
> go
> SELECT * FROM tbl
> go
> SETUSER
> go
> DROP TABLE tbl
> EXEC sp_dropuser accesstest
> EXEC sp_droplogin accesstest|||steve (stebo@.privat.utfors.se) writes:
> thanks for your help!
> I will try this, what do you think about using a VIEW, is this a good
> choice?

I don't know your business problem, so I cannot comment on that.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Permissions Not Visible - SQL 2005 Management Studio

I'm trying to apply some security to my database, so i've created a role, added my user to the role and then set the security rights for the role. Everything work great, the security right behave exactly as defined...
However, when I go back the the Permissions pages, the values that i've already set don't appear in the lists (the all still work). In Fact they don't show up anywhere either in the permissions for the table / sp concerned or the right for the user or role.
Is it just me, or is it a problem with SQL 2005 Management Studio? or can anybody suggest a way of finding the securty rights of any given database object?
Microsoft SQL Server 2005 Beta 2
Microsoft SQL Server Management Studio 9.00.1116.00
Regards
Gary T

Security in SQL Server 2005 is Schema based, try these articles to get started. Hope this helps.
http://www.databasejournal.com/features/mssql/article.php/3481751

http://www.windowsitpro.com/Article/ArticleID/42031/42031.html?Ad=1

|||

This has got me completely confused !!!!
In my simple world...
I would create a database role and the give this role the required rights to my database objects i.e the tables (select, insert update etc), stored procedures (execute etc). I would then create user accounts and add them to the required database roles...
CREATE ROLE [MyDB_AccessRole]
...Table def's...
GRANT SELECT ON [dbo].[MyTable] TO [MyDB_AccessRole] ..etc
...SP defs ...
GRANT EXECUTE ON [dbo].[MySP] TO [MyDB_AccessRole] ..etc
CREATE USER [DBUser] FOR LOGIN [DBUser]
EXEC sp_addrolemember N'MyDB_AccessRole, N'DBUser'

So far so good... this still all works in SQL 2005 (althought it nevers shows the rights anywhere?)
Then we have schemas...
CREATE SCHEMA [MyDB_AccessSchema] AUTHORIZATION [MyDB_AccessRole] ?

Is is true that the schema is just like the role? (i.e. A collection of database objects and permissions) if so, why do we need both.
Has anybody any simple examples (for dummies like me) that would explain the different between a role and a schema.
Having tried to create a role, I stiil don;t seem to be able to see the rights the user, role or schema has.
regards
Gary T

Permissions issue- a previous windows group still has full cube rights

Previously, a windows group was added to a 'full access users' role. It was removed and the cube was processed and deployed, replacing rights, but people in that group still have rights to see the data. They are not listed in any roles.

I've looked through all local windows groups on the supporting server and the group in question is not in any groups (ie, if they were in administrators, then they'd have full access through that). The people in the group are not administrators or domain administrators either.

Is there somewhere else I could check on the machine to better find the source of the problem and get the group removed?

Several places to check.

See if you allowed anonymous access to your cubes.
Check memebership of server-wide Admnistrators role.

It is sometimes little hard to track down amongst several groups what exactly is going on. To make sure you are looking at last version of data use SQL Management Studio to verify roles and memberships.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I just checked if other users that never had rights could see the cube and they can't. Anonymous access cannot be allowed.

The windows group "Adminstrators" was checked, but I don't see the admin role present in the dwproj area.
Where would one look besides the roles folder in the solution explorer or this kb: http://msdn2.microsoft.com/en-us/library/ms174561.aspx ? The group is not in any of these areas.

sql mgmt studio was used for examination - the cube was scripted to a file and scanned for groups and user names.

|||

In general there are 2 places to check:
One- role membership. And you check that on database role
Second - access permissions. There are several permissions on different objects. Cube, dimension ...

I also suggested you take a look at memebership of Analysis Server Administrators role and not OS Administrators group.

To access AS Administrators role right click on the Server name in SQL Management Studio and select properties and click on the Security node.

HTH

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||It seems like all of these have been checked. The server security was examined and the cube's xmla was parsed - this should cover any cube/dimension security, right?|||

2 more things.

Make sure you installed latest product update.

And finally. It is good idea to get your security model examined by someone with good knowlege of Analysis Services 2005.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||For product updates, we are on sp1. I could not find any hot fixes related to this.
I will keep investigating and will see if the problem can be recreated on a copy of the cube.

Permissions for EM

What permissions are needed to utilize Enterprise Manager?
Long story, but public role has all rights revoked, so I want to create a
new database role that has access to what it needs to get into the EM.As long as the user has server login priviliges, they 'should' be able to
use EM. And they will have access to any object with public
permissions -including any other db on the server that allows public access.
(Northwind and Pubs are allow Public access to everything, so they should be
removed from production servers.)
Then to access a specific db, they need to be provided access to the db, and
put in the desired role.
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"A McGuire" <allen.mcguire@.gmail.com.invalid> wrote in message
news:u6rZf10pGHA.3584@.TK2MSFTNGP03.phx.gbl...
> What permissions are needed to utilize Enterprise Manager?
> Long story, but public role has all rights revoked, so I want to create a
> new database role that has access to what it needs to get into the EM.
>|||Note in my initial post - I had to REVOKE all permissions from the public
roles. db_securityadmin, therefore, does not have sufficient privileges any
longer to do anything, so I want to create a new database role with the
permissions needed to allow our security administrators to do their jobs,
which is creating logins, granting privs, etc.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:u1XnaK1pGHA.2292@.TK2MSFTNGP05.phx.gbl...
> As long as the user has server login priviliges, they 'should' be able to
> use EM. And they will have access to any object with public
> permissions -including any other db on the server that allows public
> access. (Northwind and Pubs are allow Public access to everything, so they
> should be removed from production servers.)
> Then to access a specific db, they need to be provided access to the db,
> and put in the desired role.
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "A McGuire" <allen.mcguire@.gmail.com.invalid> wrote in message
> news:u6rZf10pGHA.3584@.TK2MSFTNGP03.phx.gbl...
>

Permissions for creating views

In database permissions I have granted a user rights to create a view. When she tries to save the view (even save as) it automatically wants to save it to the dbo schema and says she does not have rights to save to the dbo schema.

Two questions:

1) Can I set it up to where she can save a view to a schema which she is the owner?

2) If not, then what permissions must be set to allow her to create / save views but not be able to create, etc. other objects such as tables and stored procedures?

From the description it seems like this is a question regarding tools (most likely Management studio).

I have moved the question to the appropriate forum, but if you have additional questions regarding the permissions please let us know.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||Moved to Security|||

You will need to grant alter on the schema and create view on the database to the user who wants to create views.

T-SQL:

grant create view to user1

grant alter on schema:bo to user1

go

HTH,

-Steven Gott

SDE/T

SQL Server

Permission tracking

Hi all,
i'm facing the following situation: user rights must be granted for a very
short period of time, so that the user does his insert/update/delete job.
The problem is that there are many users who have requests and giving &
revoking permissions are taking a lot of time. Did anybody implemented an
automated tracking system, and if yes, could you give me a hint please? A
good way to implement permission tracking would be triggers but unluckily,
in Sql 2000, they can't be used on system tables...:-(
--
Tudor Sofron
MCSE, MCSA
Ipsos- NMRTudot
I am not sure what did you mean?
Do you want the users to be able INSERT/UPDATE/DELETE operations for a short
time?
What is a short time ( one an hour, two minutes) ?
I suggest you using STORED PROCEDURES for security reasons. Don't grant
access on underlying tables ,instead grant EXECUTE permissions for the users
on stored procedures that they need to be run.
"Tudor Sofron" <tsofron@.cluj.astral.rom> wrote in message
news:uc9kgMXxEHA.1988@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> i'm facing the following situation: user rights must be granted for a very
> short period of time, so that the user does his insert/update/delete job.
> The problem is that there are many users who have requests and giving &
> revoking permissions are taking a lot of time. Did anybody implemented an
> automated tracking system, and if yes, could you give me a hint please? A
> good way to implement permission tracking would be triggers but unluckily,
> in Sql 2000, they can't be used on system tables...:-(
> --
> Tudor Sofron
> MCSE, MCSA
> Ipsos- NMR
>
>|||Well...
this is the table i've created:
CREATE TABLE [User_RightsGranted] (
[User_Name] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[User_ID] [smallint] NULL ,
[User_SID] [varbinary] (85) NULL ,
[DB_Name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT N
ULL ,
[Object_Name] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
[Db_Owner] [tinyint] NULL ,
[Data_Reader] [tinyint] NULL ,
[Data_Writer] [tinyint] NULL ,
[Exec] [tinyint] NULL ,
[Select] [tinyint] NULL ,
[Insert] [tinyint] NULL ,
[Update] [tinyint] NULL ,
[Delete] [tinyint] NULL ,
[DateStart] [datetime] NOT NULL CONSTRAINT [DF_User_Rights_DateS
tart]
DEFAULT (getdate()),
[DateEnd] [datetime] NULL ,
[OpDate] [datetime] NOT NULL CONSTRAINT [DF_User_Rights_OpDate]
DEFAULT
(getdate()),
[OpUser] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NU
LL
CONSTRAINT [DF_User_Rights_OpUser] DEFAULT (suser_sname() + '.' +
host_name())
) ON [PRIMARY]
GO
The column names are pretty explicit so it's no need to explain their
function. The problem I have is that I can't automatize the whole process,
so I have to complete the table manually. So...did anybody faced such
problems, and if yes, how did you solved them? See my comments to your post
below...
Thanks,
Tudor Sofron
MCSE, MCSA
Ipsos- NMR
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eUVFySXxEHA.2040@.tk2msftngp13.phx.gbl...
> Tudot
> I am not sure what did you mean?
> Do you want the users to be able INSERT/UPDATE/DELETE operations for a
short
> time?
yes. I grant users INSERT/UPDATE/DELETE rights and complete the above table.
> What is a short time ( one an hour, two minutes) ?
about 30 minutes, after that I revoke the granted permissions...but I have
to do that manually...and update a similar table...

> I suggest you using STORED PROCEDURES for security reasons. Don't grant
> access on underlying tables ,instead grant EXECUTE permissions for the
users
> on stored procedures that they need to be run.
well...it's not that easy to implement the use of sp's...
>
>
> "Tudor Sofron" <tsofron@.cluj.astral.rom> wrote in message
> news:uc9kgMXxEHA.1988@.TK2MSFTNGP12.phx.gbl...
very[vbcol=seagreen]
job.[vbcol=seagreen]
an[vbcol=seagreen]
A[vbcol=seagreen]
unluckily,[vbcol=seagreen]
>|||Tudor
I am sorry but the table looks like a mess.
There is no primary key, lots of colums does allow NULL's

> The problem I have is that I can't automatize the whole process,
> so I have to complete the table manually
You mean that you would like to insert into the table all DML that users do?
If so, create a trigger on this table and start to manipuilate with DELETED
and INSERTED virtual tables
In my opinion I'd create an AUDIT table that will be gathering all info
about new/old columns
Something like that
create trigger tru_MyTable on MyTable after update
as
if @.@.ROWCOUNT = 0
return
insert MyAuditTable
select
i.ID
, d.MyColumn
, i.MyColumn
from
inserted i
join
deleted d on d.ID = o.Id
go
"Tudor Sofron" <tsofron@.cluj.astral.rom> wrote in message
news:%23QH8akXxEHA.3896@.TK2MSFTNGP10.phx.gbl...
> Well...
> this is the table i've created:
> CREATE TABLE [User_RightsGranted] (
> [User_Name] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

,
> [User_ID] [smallint] NULL ,
> [User_SID] [varbinary] (85) NULL ,
> [DB_Name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NO
T NULL ,
> [Object_Name] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_A
S NULL ,
> [Db_Owner] [tinyint] NULL ,
> [Data_Reader] [tinyint] NULL ,
> [Data_Writer] [tinyint] NULL ,
> [Exec] [tinyint] NULL ,
> [Select] [tinyint] NULL ,
> [Insert] [tinyint] NULL ,
> [Update] [tinyint] NULL ,
> [Delete] [tinyint] NULL ,
> [DateStart] [datetime] NOT NULL CONSTRAINT [DF_User_Rights_Da
teStart]
> DEFAULT (getdate()),
> [DateEnd] [datetime] NULL ,
> [OpDate] [datetime] NOT NULL CONSTRAINT [DF_User_Rights_OpDat
e] DEFAULT
> (getdate()),
> [OpUser] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
> CONSTRAINT [DF_User_Rights_OpUser] DEFAULT (suser_sname() + '.' +
> host_name())
> ) ON [PRIMARY]
> GO
> The column names are pretty explicit so it's no need to explain their
> function. The problem I have is that I can't automatize the whole process,
> so I have to complete the table manually. So...did anybody faced such
> problems, and if yes, how did you solved them? See my comments to your
post
> below...
> Thanks,
> Tudor Sofron
> MCSE, MCSA
> Ipsos- NMR
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eUVFySXxEHA.2040@.tk2msftngp13.phx.gbl...
> short
> yes. I grant users INSERT/UPDATE/DELETE rights and complete the above
table.
> about 30 minutes, after that I revoke the granted permissions...but I have
> to do that manually...and update a similar table...
>
grant[vbcol=seagreen]
> users
> well...it's not that easy to implement the use of sp's...
> very
> job.
&[vbcol=seagreen]
> an
please?[vbcol=seagreen]
> A
> unluckily,
>|||well...the table design is in 'development phase' :-)...but i hope that soon
this will be done.
Tudor Sofron
MCSE, MCSA
Ipsos- NMR
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uuqlkwXxEHA.1264@.TK2MSFTNGP12.phx.gbl...
> Tudor
> I am sorry but the table looks like a mess.
> There is no primary key, lots of colums does allow NULL's
>
>
> You mean that you would like to insert into the table all DML that users
do?
> If so, create a trigger on this table and start to manipuilate with
DELETED
> and INSERTED virtual tables
> In my opinion I'd create an AUDIT table that will be gathering all info
> about new/old columns
> Something like that
> create trigger tru_MyTable on MyTable after update
> as
> if @.@.ROWCOUNT = 0
> return
> insert MyAuditTable
> select
> i.ID
> , d.MyColumn
> , i.MyColumn
> from
> inserted i
> join
> deleted d on d.ID = o.Id
> go
>
>
> "Tudor Sofron" <tsofron@.cluj.astral.rom> wrote in message
> news:%23QH8akXxEHA.3896@.TK2MSFTNGP10.phx.gbl...
NULL[vbcol=seagreen]
> ,
,[vbcol=seagreen]
,[vbcol=seagreen]
process,[vbcol=seagreen]
> post
> table.
have[vbcol=seagreen]
> grant
a[vbcol=seagreen]
giving[vbcol=seagreen]
> &
implemented[vbcol=seagreen]
> please?
>

Permission to run DBCC DROPCLEANBUFFERS

Were using SQL Server 2000.
Whilst the devevelopers have reasonably good access to do most things on the
Dev box, we are not granted SA rights.
For performance tuning of queries, I need to be able to run DBCC
DROPCLEANBUFFERS but apparently you need to be SA to do this.
Does anybody know how we can get around this so the DBA's can affectivly
give me these permissions without making me SA.
Any help would be great.
Thanks,
JoeI'm pretty sure that only sysadmin role members can execute DBCC
DROPCLEANBUFFERS. That's the requirement in SQL 2005 so the same probably
applies to SQL 2000.
Hope this helps.
Dan Guzman
SQL Server MVP
"KCSL" <KCSL@.discussions.microsoft.com> wrote in message
news:AE187C22-F0DC-44C9-902B-D87C14CCD8B6@.microsoft.com...
> Were using SQL Server 2000.
> Whilst the devevelopers have reasonably good access to do most things on
> the
> Dev box, we are not granted SA rights.
> For performance tuning of queries, I need to be able to run DBCC
> DROPCLEANBUFFERS but apparently you need to be SA to do this.
> Does anybody know how we can get around this so the DBA's can affectivly
> give me these permissions without making me SA.
> Any help would be great.
> Thanks,
> Joe
>|||Not possible. You have to be an member of the sysadmin role to issue that
command. There is a reason for that. You are going to purge every buffer
in the system which will force SQL Server to read everything back off disk.
Only a sysadmin should have the authority to make that decision.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"KCSL" <KCSL@.discussions.microsoft.com> wrote in message
news:AE187C22-F0DC-44C9-902B-D87C14CCD8B6@.microsoft.com...
> Were using SQL Server 2000.
> Whilst the devevelopers have reasonably good access to do most things on
> the
> Dev box, we are not granted SA rights.
> For performance tuning of queries, I need to be able to run DBCC
> DROPCLEANBUFFERS but apparently you need to be SA to do this.
> Does anybody know how we can get around this so the DBA's can affectivly
> give me these permissions without making me SA.
> Any help would be great.
> Thanks,
> Joe
>|||"KCSL" <KCSL@.discussions.microsoft.com> wrote in message
news:AE187C22-F0DC-44C9-902B-D87C14CCD8B6@.microsoft.com...
> Were using SQL Server 2000.
> Whilst the devevelopers have reasonably good access to do most things on
> the
> Dev box, we are not granted SA rights.
> For performance tuning of queries, I need to be able to run DBCC
> DROPCLEANBUFFERS but apparently you need to be SA to do this.
> Does anybody know how we can get around this so the DBA's can affectivly
> give me these permissions without making me SA.
>
You shouldn't be using DBCC DROPCLEANBUFFERS for performance tuning.
SET STATISTICS IO ON
SET STATISTICS TIME ON
Give you better information. With the large memory sizes of servers, you
never have purged buffers, and your query performance in that situration is
not meaningful. To tune queries the most important metric is amount of
logical IO. You can measure that on production systems or test systems, and
it is very, very highly correlated with elapsed time and CPU utilization.
If you minimize logical IO, you will minimize query cost.
David

Permission settings for views to be created

Hi,
I'm a beginner when it comes to SQL Server 2005 and user permissions.
I'm wondering, how do I set the default rights for views yet to be
created? I've only find a way to configure the already existing views.
Br,
Tom...What I need is to have a user that can only access the views in a
database (meaning access the data in tables through views).
Tom wrote:
> Hi,
> I'm a beginner when it comes to SQL Server 2005 and user permissions.
> I'm wondering, how do I set the default rights for views yet to be
> created? I've only find a way to configure the already existing views.
> Br,
> Tom|||Tom
GRANT SELECT on SCHENA ...... GRANT SELECT on VIEW .... . (See details
in the BOL) .
Do the users have SSMS installed on their workstations?
"Tom" <tom.kostiainen@.saunalahti.fi> wrote in message
news:1163166994.843815.142270@.h48g2000cwc.googlegroups.com...
> Hi,
> I'm a beginner when it comes to SQL Server 2005 and user permissions.
> I'm wondering, how do I set the default rights for views yet to be
> created? I've only find a way to configure the already existing views.
> Br,
> Tom
>|||Using a schema I can deny or grant permissions to use the alter
command, but not specifically grant alter for any view (existing or to
be created) AND deny alter for any table. I can only either grant or
deny alter, right?
Do I really have to specifically set permissions for every table and
view and not just specify a default setting for all views and table in
a database?
Uri Dimant wrote:[vbcol=seagreen]
> Tom
> GRANT SELECT on SCHENA ...... GRANT SELECT on VIEW .... . (See details
> in the BOL) .
> Do the users have SSMS installed on their workstations?
>
> "Tom" <tom.kostiainen@.saunalahti.fi> wrote in message
> news:1163166994.843815.142270@.h48g2000cwc.googlegroups.com...|||Tom
> Do I really have to specifically set permissions for every table and
> view and not just specify a default setting for all views and table in
> a database?
You can GRANT SELECT ... on database level , take a look at GRANT topic in
the BOL
"Tom" <tom.kostiainen@.saunalahti.fi> wrote in message
news:1163403167.187392.286270@.m7g2000cwm.googlegroups.com...
> Using a schema I can deny or grant permissions to use the alter
> command, but not specifically grant alter for any view (existing or to
> be created) AND deny alter for any table. I can only either grant or
> deny alter, right?
> Do I really have to specifically set permissions for every table and
> view and not just specify a default setting for all views and table in
> a database?
>
> Uri Dimant wrote:
>

Permission issue

Hello,
Some facts:
- I am using SQL Server 2000 on W2K3
- I have a SQL Login called 'Rlogic' with dbo permission rights on every
database. It is also a member of the Systems Administrators Group.
In Query Analyzer, when I run the following:-
use Rlogic_Training
go
select *
from users
I get this error:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'users'.
However, when I append 'Rlogic.' before the dbname, it works fine: -
use Rlogic_Training
go
select *
from Rlogic.users
Is there a way for me NOT to append 'Rlogic.' everytime?
Thanks,
Sameer
Message posted via http://www.droptable.com
Hello,
In SQL 2005 you can create a SYNONYM and query the Synonym. But for SQL
2005, you need to either login as object owner or you need to change the
object owner to DBO using
SP_CHANGEOBJECTOWNER system stored procedure. If the object owner is DBO you
do not want to prefix the object owner even if you login as a different
user.
Thanks
Hari
"spremji via droptable.com" <u4996@.uwe> wrote in message
news:6fd4c5051b6c0@.uwe...
> Hello,
> Some facts:
> - I am using SQL Server 2000 on W2K3
> - I have a SQL Login called 'Rlogic' with dbo permission rights on every
> database. It is also a member of the Systems Administrators Group.
> In Query Analyzer, when I run the following:-
> use Rlogic_Training
> go
> select *
> from users
>
> I get this error:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'users'.
>
> However, when I append 'Rlogic.' before the dbname, it works fine: -
> use Rlogic_Training
> go
> select *
> from Rlogic.users
> Is there a way for me NOT to append 'Rlogic.' everytime?
> Thanks,
> Sameer
> --
> Message posted via http://www.droptable.com
>
|||Hello Hari,
I tried sp_changeobjectowner but got this error:
Server: Msg 15001, Level 16, State 1, Procedure sp_changeobjectowner, Line 38
Object 'USERS' does not exist or is not a valid object for this operation.
When I see the tables in the 'Object Browser' left pane, I see them starting
with prefix 'Rlogic.' and there are literally hundreds of these tables and
there are few tables that start with 'dbo.' which I can query them without
any problem.
Is there a way to reflect this change to all these tables?
Thanks.
Message posted via http://www.droptable.com
|||Sorry, ignore the above error but the exact error I got was: -
Server: Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line 47
The proposed new database owner is already a user in the database.
Message posted via http://www.droptable.com
|||Hi
Probably you cereated this table before the login was added to SysAdmin
server role, right?
Well , create a new table ,move the data from the old one to the new one,
drop the old table then rename the new table with original name
"spremji via droptable.com" <u4996@.uwe> wrote in message
news:6fd4c5051b6c0@.uwe...
> Hello,
> Some facts:
> - I am using SQL Server 2000 on W2K3
> - I have a SQL Login called 'Rlogic' with dbo permission rights on every
> database. It is also a member of the Systems Administrators Group.
> In Query Analyzer, when I run the following:-
> use Rlogic_Training
> go
> select *
> from users
>
> I get this error:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'users'.
>
> However, when I append 'Rlogic.' before the dbname, it works fine: -
> use Rlogic_Training
> go
> select *
> from Rlogic.users
> Is there a way for me NOT to append 'Rlogic.' everytime?
> Thanks,
> Sameer
> --
> Message posted via http://www.droptable.com
>
|||Hello,
PLease do not execute SP_CHANGEDBOWNER. You may need to use
SP_CHANGEOBJECTOWNER system procedure to change the object owner to DBO.
Use the below sample:-
Use <DBNAME>
GO
exec sp_changeobjectowner 'Rlogic.Users', 'dbo'
Thanks
Hari
"spremji via droptable.com" <u4996@.uwe> wrote in message
news:6fd64dd4931f0@.uwe...
> Sorry, ignore the above error but the exact error I got was: -
> Server: Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line 47
> The proposed new database owner is already a user in the database.
> --
> Message posted via http://www.droptable.com
>
|||Uri,
No, the tables have been previously created by the Software installer and I
don't have the luxury to recreate the tables.
I have a db user 'Rlogic' which is associated with SQL Login 'Rlogic' as well
and they are both associated fine.
For some reason, the user 'Rlogic' is not able to query the tables despite
being the db owner.
Any help is appreciated as clients are waiting to work and no application is
able to login.
Thanks,
Sameer
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200703/1
|||OK....phew, problem was solved because the 'Rlogic' login was a member of
'Systems Administrators' group.
Hence, this login would log into the database with 'dbo' rights rather than
"user" named 'Rlogic'.
Thanks for the responses
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200703/1

Permission issue

Hello,
Some facts:
- I am using SQL Server 2000 on W2K3
- I have a SQL Login called 'Rlogic' with dbo permission rights on every
database. It is also a member of the Systems Administrators Group.
In Query Analyzer, when I run the following:-
use Rlogic_Training
go
select *
from users
I get this error:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'users'.
---
However, when I append 'Rlogic.' before the dbname, it works fine: -
use Rlogic_Training
go
select *
from Rlogic.users
Is there a way for me NOT to append 'Rlogic.' everytime?
Thanks,
Sameer
--
Message posted via http://www.sqlmonster.comHello,
In SQL 2005 you can create a SYNONYM and query the Synonym. But for SQL
2005, you need to either login as object owner or you need to change the
object owner to DBO using
SP_CHANGEOBJECTOWNER system stored procedure. If the object owner is DBO you
do not want to prefix the object owner even if you login as a different
user.
Thanks
Hari
"spremji via SQLMonster.com" <u4996@.uwe> wrote in message
news:6fd4c5051b6c0@.uwe...
> Hello,
> Some facts:
> - I am using SQL Server 2000 on W2K3
> - I have a SQL Login called 'Rlogic' with dbo permission rights on every
> database. It is also a member of the Systems Administrators Group.
> In Query Analyzer, when I run the following:-
> use Rlogic_Training
> go
> select *
> from users
>
> I get this error:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'users'.
> ---
>
> However, when I append 'Rlogic.' before the dbname, it works fine: -
> use Rlogic_Training
> go
> select *
> from Rlogic.users
> Is there a way for me NOT to append 'Rlogic.' everytime?
> Thanks,
> Sameer
> --
> Message posted via http://www.sqlmonster.com
>|||Hello Hari,
I tried sp_changeobjectowner but got this error:
Server: Msg 15001, Level 16, State 1, Procedure sp_changeobjectowner, Line 38
Object 'USERS' does not exist or is not a valid object for this operation.
When I see the tables in the 'Object Browser' left pane, I see them starting
with prefix 'Rlogic.' and there are literally hundreds of these tables and
there are few tables that start with 'dbo.' which I can query them without
any problem.
Is there a way to reflect this change to all these tables?
Thanks.
--
Message posted via http://www.sqlmonster.com|||Sorry, ignore the above error but the exact error I got was: -
Server: Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line 47
The proposed new database owner is already a user in the database.
--
Message posted via http://www.sqlmonster.com|||Hi
Probably you cereated this table before the login was added to SysAdmin
server role, right?
Well , create a new table ,move the data from the old one to the new one,
drop the old table then rename the new table with original name
"spremji via SQLMonster.com" <u4996@.uwe> wrote in message
news:6fd4c5051b6c0@.uwe...
> Hello,
> Some facts:
> - I am using SQL Server 2000 on W2K3
> - I have a SQL Login called 'Rlogic' with dbo permission rights on every
> database. It is also a member of the Systems Administrators Group.
> In Query Analyzer, when I run the following:-
> use Rlogic_Training
> go
> select *
> from users
>
> I get this error:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'users'.
> ---
>
> However, when I append 'Rlogic.' before the dbname, it works fine: -
> use Rlogic_Training
> go
> select *
> from Rlogic.users
> Is there a way for me NOT to append 'Rlogic.' everytime?
> Thanks,
> Sameer
> --
> Message posted via http://www.sqlmonster.com
>|||Hello,
PLease do not execute SP_CHANGEDBOWNER. You may need to use
SP_CHANGEOBJECTOWNER system procedure to change the object owner to DBO.
Use the below sample:-
Use <DBNAME>
GO
exec sp_changeobjectowner 'Rlogic.Users', 'dbo'
Thanks
Hari
"spremji via SQLMonster.com" <u4996@.uwe> wrote in message
news:6fd64dd4931f0@.uwe...
> Sorry, ignore the above error but the exact error I got was: -
> Server: Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line 47
> The proposed new database owner is already a user in the database.
> --
> Message posted via http://www.sqlmonster.com
>|||Uri,
No, the tables have been previously created by the Software installer and I
don't have the luxury to recreate the tables.
I have a db user 'Rlogic' which is associated with SQL Login 'Rlogic' as well
and they are both associated fine.
For some reason, the user 'Rlogic' is not able to query the tables despite
being the db owner.
Any help is appreciated as clients are waiting to work and no application is
able to login.
Thanks,
Sameer
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1|||OK....phew, problem was solved because the 'Rlogic' login was a member of
'Systems Administrators' group.
Hence, this login would log into the database with 'dbo' rights rather than
"user" named 'Rlogic'.
Thanks for the responses :)
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1

Permission issue

Hello,
Some facts:
- I am using SQL Server 2000 on W2K3
- I have a SQL Login called 'Rlogic' with dbo permission rights on every
database. It is also a member of the Systems Administrators Group.
In Query Analyzer, when I run the following:-
use Rlogic_Training
go
select *
from users
I get this error:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'users'.
---
However, when I append 'Rlogic.' before the dbname, it works fine: -
use Rlogic_Training
go
select *
from Rlogic.users
Is there a way for me NOT to append 'Rlogic.' everytime?
Thanks,
Sameer
Message posted via http://www.droptable.comHello,
In SQL 2005 you can create a SYNONYM and query the Synonym. But for SQL
2005, you need to either login as object owner or you need to change the
object owner to DBO using
SP_CHANGEOBJECTOWNER system stored procedure. If the object owner is DBO you
do not want to prefix the object owner even if you login as a different
user.
Thanks
Hari
"spremji via droptable.com" <u4996@.uwe> wrote in message
news:6fd4c5051b6c0@.uwe...
> Hello,
> Some facts:
> - I am using SQL Server 2000 on W2K3
> - I have a SQL Login called 'Rlogic' with dbo permission rights on every
> database. It is also a member of the Systems Administrators Group.
> In Query Analyzer, when I run the following:-
> use Rlogic_Training
> go
> select *
> from users
>
> I get this error:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'users'.
> ---
>
> However, when I append 'Rlogic.' before the dbname, it works fine: -
> use Rlogic_Training
> go
> select *
> from Rlogic.users
> Is there a way for me NOT to append 'Rlogic.' everytime?
> Thanks,
> Sameer
> --
> Message posted via http://www.droptable.com
>|||Hello Hari,
I tried sp_changeobjectowner but got this error:
Server: Msg 15001, Level 16, State 1, Procedure sp_changeobjectowner, Line 3
8
Object 'USERS' does not exist or is not a valid object for this operation.
When I see the tables in the 'Object Browser' left pane, I see them starting
with prefix 'Rlogic.' and there are literally hundreds of these tables and
there are few tables that start with 'dbo.' which I can query them without
any problem.
Is there a way to reflect this change to all these tables?
Thanks.
Message posted via http://www.droptable.com|||Sorry, ignore the above error but the exact error I got was: -
Server: Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line 47
The proposed new database owner is already a user in the database.
Message posted via http://www.droptable.com|||Hi
Probably you cereated this table before the login was added to SysAdmin
server role, right?
Well , create a new table ,move the data from the old one to the new one,
drop the old table then rename the new table with original name
"spremji via droptable.com" <u4996@.uwe> wrote in message
news:6fd4c5051b6c0@.uwe...
> Hello,
> Some facts:
> - I am using SQL Server 2000 on W2K3
> - I have a SQL Login called 'Rlogic' with dbo permission rights on every
> database. It is also a member of the Systems Administrators Group.
> In Query Analyzer, when I run the following:-
> use Rlogic_Training
> go
> select *
> from users
>
> I get this error:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'users'.
> ---
>
> However, when I append 'Rlogic.' before the dbname, it works fine: -
> use Rlogic_Training
> go
> select *
> from Rlogic.users
> Is there a way for me NOT to append 'Rlogic.' everytime?
> Thanks,
> Sameer
> --
> Message posted via http://www.droptable.com
>|||Hello,
PLease do not execute SP_CHANGEDBOWNER. You may need to use
SP_CHANGEOBJECTOWNER system procedure to change the object owner to DBO.
Use the below sample:-
Use <DBNAME>
GO
exec sp_changeobjectowner 'Rlogic.Users', 'dbo'
Thanks
Hari
"spremji via droptable.com" <u4996@.uwe> wrote in message
news:6fd64dd4931f0@.uwe...
> Sorry, ignore the above error but the exact error I got was: -
> Server: Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line 47
> The proposed new database owner is already a user in the database.
> --
> Message posted via http://www.droptable.com
>|||Uri,
No, the tables have been previously created by the Software installer and I
don't have the luxury to recreate the tables.
I have a db user 'Rlogic' which is associated with SQL Login 'Rlogic' as wel
l
and they are both associated fine.
For some reason, the user 'Rlogic' is not able to query the tables despite
being the db owner.
Any help is appreciated as clients are waiting to work and no application is
able to login.
Thanks,
Sameer
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200703/1|||OK....phew, problem was solved because the 'Rlogic' login was a member of
'Systems Administrators' group.
Hence, this login would log into the database with 'dbo' rights rather than
"user" named 'Rlogic'.
Thanks for the responses
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200703/1

Permission clarification - dbo vs domain\user.dbo

Our developers have been given dbcreator rights on the server and have dbo
rights for a given database. This is a slight change from what was
previously dolled out as rights on the database. When the developer create
s
a view, rather than the view being created as dbo.myview, it is created as
Domain\developername.dbo.myview. I am not sure this is what I want. Is
there a permission I can add that will allow the developer to create DBO onl
y
views on the given database?
--
Regards,
JamieBOL says:
The dbo is a user that has implied permissions to perform all activities in
the database. Any member of the sysadmin fixed server role who uses a
database is mapped to the special user inside each database called dbo.
Also, any object created by any member of the sysadmin fixed server role
belongs to dbo automatically.
For example, if user Andrew is a member of the sysadmin fixed server role
and creates a table T1, T1 belongs to dbo and is qualified as dbo.T1, not as
Andrew.T1. Conversely, if Andrew is not a member of the sysadmin fixed
server role but is a member only of the db_owner fixed database role and
creates a table T1, T1 belongs to Andrew and is qualified as Andrew.T1. The
table belongs to Andrew because he did not qualify the table as dbo.T1.
The dbo user cannot be deleted and is always present in every database.
Only objects created by members of the sysadmin fixed server role (or by the
dbo user) belong to dbo. Objects created by any other user who is not also a
member of the sysadmin fixed server role (including members of the db_owner
fixed database role):
a.. Belong to the user creating the object, not dbo.
b.. Are qualified with the name of the user who created the object.
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:051962BF-4DDA-4FDC-BF7B-C2B241C1E3DE@.microsoft.com...
> Our developers have been given dbcreator rights on the server and have dbo
> rights for a given database. This is a slight change from what was
> previously dolled out as rights on the database. When the developer
> creates
> a view, rather than the view being created as dbo.myview, it is created as
> Domain\developername.dbo.myview. I am not sure this is what I want.
> Is
> there a permission I can add that will allow the developer to create DBO
> only
> views on the given database?
> --
> Regards,
> Jamie