granting permission to create view

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

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

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

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

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

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

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

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

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

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

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

-David

Grant Views to User

Hi all,

I have a question on the grant issue. I have created a view base on three tables, and I grant 'select' to the user to see the views I have created. But when the user select this views, it say :
"SELECT permission denied on object 'Table1', database 'XX', owner 'XX'.
SELECT permission denied on object 'Table2', database 'XX', owner 'XX'.
SELECT permission denied on object 'Table3', database 'XX', owner 'XX'."

I grant this three table to the user, and it run successfully.

Question is whether is there a way I can let user run the views but without seeing the three tables? or is there a way to hide from user?

Thanks in advance.

Are the tables in another database?

If the tables are in another database than the user, see if this applies (from BOL)

Cross-database permissions are not allowed; permissions can be granted only to users in the current database for objects and statements in the current database. If a user needs permissions to objects in another database, create the user account in the other database, or grant the user account access to the other database, as well as the current database.

/Kenneth

|||

Hi Kenneth,

The views and tables are in the same database. But even in same database it give me the same problem that I have mention in my first post on this topic.

Thanks.

|||Are the views and the table owned by different users ? If they are owned by different users then you should see this error, otherwise it will be a security violation.|||

Hi,

Ya, the tables is created using one user account and the views is created by another user account.

So you mean that I need to create the views using the user account which create the tables? Or is there any other way to solve this?

Thanks.

|||

The 'old way' to avoid this kind of problems (broken ownership chains), is to have all objects in the database
being owned by the same user - eg 'dbo'

The easy way to accomplish that, is to always create objects with two-part names (dbo.myNewtable, dbo.myProc etc..)

/Kenneth

Grant Views to User

Hi all,

I have a question on the grant issue. I have created a view base on three tables, and I grant 'select' to the user to see the views I have created. But when the user select this views, it say :
"SELECT permission denied on object 'Table1', database 'XX', owner 'XX'.
SELECT permission denied on object 'Table2', database 'XX', owner 'XX'.
SELECT permission denied on object 'Table3', database 'XX', owner 'XX'."

I grant this three table to the user, and it run successfully.

Question is whether is there a way I can let user run the views but without seeing the three tables? or is there a way to hide from user?

Thanks in advance.

Are the tables in another database?

If the tables are in another database than the user, see if this applies (from BOL)

Cross-database permissions are not allowed; permissions can be granted only to users in the current database for objects and statements in the current database. If a user needs permissions to objects in another database, create the user account in the other database, or grant the user account access to the other database, as well as the current database.

/Kenneth

|||

Hi Kenneth,

The views and tables are in the same database. But even in same database it give me the same problem that I have mention in my first post on this topic.

Thanks.

|||Are the views and the table owned by different users ? If they are owned by different users then you should see this error, otherwise it will be a security violation.|||

Hi,

Ya, the tables is created using one user account and the views is created by another user account.

So you mean that I need to create the views using the user account which create the tables? Or is there any other way to solve this?

Thanks.

|||

The 'old way' to avoid this kind of problems (broken ownership chains), is to have all objects in the database
being owned by the same user - eg 'dbo'

The easy way to accomplish that, is to always create objects with two-part names (dbo.myNewtable, dbo.myProc etc..)

/Kenneth

GRANT SELECT on View

I have granted SELECT permissions on a view to a user, now when I run the
view it tells me that the user is denied select permissions on a table. The
user does not have any other permissions set on any tables or views other
than the SELECT on this particular view. How can I allow the user to view
this data?
Thank you in advance,
RuiThis sounds like a broken ownership problem. Does the same user (e.g. dbo)
own the view as owns the table? To avoid this problem, both objects will
need to be owned by the same user.
"Rui Peres" <peresr@.southcoast.org> wrote in message
news:u2quzdN0FHA.2960@.tk2msftngp13.phx.gbl...
>I have granted SELECT permissions on a view to a user, now when I run the
> view it tells me that the user is denied select permissions on a table.
> The
> user does not have any other permissions set on any tables or views other
> than the SELECT on this particular view. How can I allow the user to view
> this data?
> Thank you in advance,
> Rui
>|||Thanks, that did it.
Rui
"Lori Clark" <lclark@.dbadvisor.com> wrote in message
news:ebAkZvN0FHA.460@.TK2MSFTNGP15.phx.gbl...
> This sounds like a broken ownership problem. Does the same user (e.g.
dbo)
> own the view as owns the table? To avoid this problem, both objects will
> need to be owned by the same user.
>
> "Rui Peres" <peresr@.southcoast.org> wrote in message
> news:u2quzdN0FHA.2960@.tk2msftngp13.phx.gbl...
other[vbcol=seagreen]
view[vbcol=seagreen]
>

Grant permissions

Hi all,
Can I grant select only permission on all objects in the database? I have users that I need to give view access only on stored procedures, triggers, and functions. Thanks.db_datareader (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_6ndx.asp).

-PatPsql

Grant job view and execute

Hello,
How can i grant a user to view and execute Jobs on SqlSrv2K without grant
SystemAdministratr previleges.
Thanks
Leandro Loureiro dos SantosLeonardo
BOL says
How to give others ownership of a job (Enterprise Manager)
To give others ownership of a job
1.. In the details pane, right-click the job, and then click Properties.
2.. In the Owner list, select a login.
Assigning a job to another login does not guarantee that the new owner has
sufficient permission to run the job successfully.
"Leandro Loureiro dos Santos" <leandro@.email.com> wrote in message
news:uD5wcNH4DHA.2380@.TK2MSFTNGP11.phx.gbl...
> Hello,
> How can i grant a user to view and execute Jobs on SqlSrv2K without grant
> SystemAdministratr previleges.
> Thanks
> Leandro Loureiro dos Santos
>|||Actually, i need non-system administrators users could view and execute a
job.
How can i do that?
"Leandro Loureiro dos Santos" <leandro@.email.com> escreveu na mensagem
news:uD5wcNH4DHA.2380@.TK2MSFTNGP11.phx.gbl...
> Hello,
> How can i grant a user to view and execute Jobs on SqlSrv2K without grant
> SystemAdministratr previleges.
> Thanks
> Leandro Loureiro dos Santos
>

Grant job view and execute

Hello,
How can i grant a user to view and execute Jobs on SqlSrv2K without grant
SystemAdministratr previleges.
Thanks
Leandro Loureiro dos SantosLeonardo
BOL says
How to give others ownership of a job (Enterprise Manager)
To give others ownership of a job
1.. In the details pane, right-click the job, and then click Properties.
2.. In the Owner list, select a login.
Assigning a job to another login does not guarantee that the new owner has
sufficient permission to run the job successfully.
"Leandro Loureiro dos Santos" <leandro@.email.com> wrote in message
news:uD5wcNH4DHA.2380@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Hello,
> How can i grant a user to view and execute Jobs on SqlSrv2K without grant
> SystemAdministratr previleges.
> Thanks
> Leandro Loureiro dos Santos
>

---

|||Actually, i need non-system administrators users could view and execute a
job.
How can i do that?
"Leandro Loureiro dos Santos" <leandro@.email.com> escreveu na mensagem
news:uD5wcNH4DHA.2380@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Hello,
> How can i grant a user to view and execute Jobs on SqlSrv2K without grant
> SystemAdministratr previleges.
> Thanks
> Leandro Loureiro dos Santos
>

---

sql

Grant CREATE VIEW, CREATE PROCEDURE ...

Hi,

I have currently a problem with setting up the permissions for some developers. My configuration looks like this.

DB A is the productive database.

DB B is a kind of "development" database.

Now we have a couple of users call them BOB, DAVID, ...

who are members of the db role db_reader and db_writer for the productive db a but they should be allowed to do nearly everything on db b.

Therefor I added them to the db role db_owner for db b.

For testing purposes I tried to "CREATE" a view TEST as BOB in database B but I received the error message

'Msg 262, Level 14, State 1, Procedure Test, Line 3

CREATE VIEW permission denied in database 'b'.'

I cross checked the permissions on db level and I even granted all available permissions on db level but nevertheless I receive this error message.

What's my mistake?

Of course it worked fine when I give them sysadmin rights but then they have far too much permissions.

Regards,

Stefan

Hi Stefan,

If you made sure that you granted them the needed permissions, you possibly revoked/denied some permissions to them. Look at the database level, if they are able to create / alter / drop a view. Denieing overwrite any granted special rights.

HTH, jens Suessmeyer.

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

Hi Jens,

unfortunately no . I didn't revoke or deny any permissions. The specific users are members of db_owner ..and in addition I tried to grant them all permissions on the db level. There is no permission denied.

Might it be a problem that the default schema is dbo?

Regards,

Stefan

|||Which version of SQL Server are you using ? Can you post the header of your creation script here ?

HTH, jens Suessmeyer.

http://www.sqlserver2005.de
|||Besideyour original problem, why didn′t you put him in the db_ddladmin group, that should be *normally* sufficient to create objects.

HTH, Jens Suessmeyer

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

Hi Jens,

I'm using SQL Server 2005 in an Enterprise Edition.

Currently I was trying to set up a very small sample db with the same conditions on a Developer Edition on my notebook ...strange thing it worked with the membership in the role db_owner. Now I'm wondering where there is the difference ...since with my last attempt I granted the user on the "server" database all available server and db level permissions.

Question:

The orignal db has been migrated (by detach and attach) from SQL Server 2000 about a week ago. Could there be any condition that settings from the old 2000 DB might have a bad influence on the migrated 2005 version?

Regards,

Stefan

|||Hi,

not directly, but if you detach and attach a db, the server logins are created by default. So you have to drop and recreate the users again OR rempa the server logins to the database users.

HTH, jens Suessmeyer.

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

Hi Jens,

this morning I was trying to compare my development db with the server db and I was not able to find the differences in the permissions. Therefor ...and to avoid any long search for possible reasons I decided to drop all development users and to reset all server and db level permissions to standard. Afterwards I started setting all the permissions after our db documentation again ...and finally I succeded.

After having reset and set all permission from scratch and recreated the users it works now.

So I assume your first assumption was correct that somewhere/somehow one or more necessary permissions have been denied.

Thank you very much for your help,

Stefan

Grant create proc to

Hello all,
Is there a place to view in SQL Management studio that I have geranted
create proc rights to a certain user?
TIA
> Is there a place to view in SQL Management studio that I have geranted
> create proc rights to a certain user?
You can run a query like the following:
USE <database> -- The database the user has permissions in.
--Set the session context to the user.
EXECUTE AS User = '<user_name>';
GO
-- Get the user's permissions on the current database
SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
GO
-- Set the session context back to you.
REVERT;
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:2A8498AB-96BE-49EF-B894-E9EDB3D8F5CB@.microsoft.com...
> Hello all,
> Is there a place to view in SQL Management studio that I have geranted
> create proc rights to a certain user?
>
> TIA

Grant create proc to

Hello all,
Is there a place to view in SQL Management studio that I have geranted
create proc rights to a certain user?
TIA> Is there a place to view in SQL Management studio that I have geranted
> create proc rights to a certain user?
You can run a query like the following:
USE <database> -- The database the user has permissions in.
--Set the session context to the user.
EXECUTE AS User = '<user_name>';
GO
-- Get the user's permissions on the current database
SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
GO
-- Set the session context back to you.
REVERT;
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pr...oads/books.mspx
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:2A8498AB-96BE-49EF-B894-E9EDB3D8F5CB@.microsoft.com...
> Hello all,
> Is there a place to view in SQL Management studio that I have geranted
> create proc rights to a certain user?
>
> TIA

Grant create proc to

Hello all,
Is there a place to view in SQL Management studio that I have geranted
create proc rights to a certain user?
TIA> Is there a place to view in SQL Management studio that I have geranted
> create proc rights to a certain user?
You can run a query like the following:
USE <database> -- The database the user has permissions in.
--Set the session context to the user.
EXECUTE AS User = '<user_name>';
GO
-- Get the user's permissions on the current database
SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
GO
-- Set the session context back to you.
REVERT;
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:2A8498AB-96BE-49EF-B894-E9EDB3D8F5CB@.microsoft.com...
> Hello all,
> Is there a place to view in SQL Management studio that I have geranted
> create proc rights to a certain user?
>
> TIAsql

GRANT ALTER TRACE in MSSQL 2000

Hi,
How do I acomplish the following in MSSQL Server 2000?
GRANT ALTER TRACE TO <user>;
GRANT VIEW ANY DEFINITION TO <user>;
Thanks
KrassHi Krass
The first is not possible, the second is not necessary.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"kkantchev@.hotmail.com" <kkantchevhotmailcom@.discussions.microsoft.com>
wrote in message news:E3282092-9BAA-4025-8A8B-1086356E3464@.microsoft.com...
> Hi,
> How do I acomplish the following in MSSQL Server 2000?
> GRANT ALTER TRACE TO <user>;
> GRANT VIEW ANY DEFINITION TO <user>;
> Thanks
> Krass|||Thanks Kalen,
So, I guess user needs to have sysadmin role. Is that correct?
Krass
"Kalen Delaney" wrote:

> Hi Krass
> The first is not possible, the second is not necessary.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "kkantchev@.hotmail.com" <kkantchevhotmailcom@.discussions.microsoft.com>
> wrote in message news:E3282092-9BAA-4025-8A8B-1086356E3464@.microsoft.com..
.
>
>|||Krass,
On SQL Server 2000, only sysadmins can run SQL Traces.
RLF
"kkantchev@.hotmail.com" <kkantchevhotmailcom@.discussions.microsoft.com>
wrote in message news:6EB6CD69-8E98-4A64-96CB-DDD4316C6036@.microsoft.com...[vbcol=seagreen]
> Thanks Kalen,
> So, I guess user needs to have sysadmin role. Is that correct?
> Krass
> "Kalen Delaney" wrote:
>

Grabbing CURRENT Parameter Data

So it looks like when you add an expression which grabs a parameter value
(Parameters!SRNumber.Value), it grabs the data from when the "View Report"
button was originally pressed. My goal is to allow someone to punch in new
data into a parameter and without having to re-run the report, be able to use
that for an action. Any ideas? Example of what I am attempting to do below.
= "mailto:" + Fields!SOURCE_EMAIL.Value + "?subject=SR #"
+Parameters!SRNumber.Value + " has been assigned to you"...
(SRNumber is a blank parameter which isn't actually used in any of my
queries, but rather just there for this purpose)On Dec 12, 5:25 pm, Peetaah <Peet...@.discussions.microsoft.com> wrote:
> So it looks like when you add an expression which grabs a parameter value
> (Parameters!SRNumber.Value), it grabs the data from when the "View Report"
> button was originally pressed. My goal is to allow someone to punch in new
> data into a parameter and without having to re-run the report, be able to use
> that for an action. Any ideas? Example of what I am attempting to do below.
> = "mailto:" + Fields!SOURCE_EMAIL.Value + "?subject=SR #"
> +Parameters!SRNumber.Value + " has been assigned to you"...
> (SRNumber is a blank parameter which isn't actually used in any of my
> queries, but rather just there for this purpose)
This is a long shot, but you could try using javascript. Normally, the
last value selected before the View Report button is selected is shown
in an expression based on a parameter. Sorry that I could not be of
further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks, that is what I was thinking too-- just hoping there was a secret call
I could make to get the current value :)
"EMartinez" wrote:
> On Dec 12, 5:25 pm, Peetaah <Peet...@.discussions.microsoft.com> wrote:
> > So it looks like when you add an expression which grabs a parameter value
> > (Parameters!SRNumber.Value), it grabs the data from when the "View Report"
> > button was originally pressed. My goal is to allow someone to punch in new
> > data into a parameter and without having to re-run the report, be able to use
> > that for an action. Any ideas? Example of what I am attempting to do below.
> >
> > = "mailto:" + Fields!SOURCE_EMAIL.Value + "?subject=SR #"
> > +Parameters!SRNumber.Value + " has been assigned to you"...
> > (SRNumber is a blank parameter which isn't actually used in any of my
> > queries, but rather just there for this purpose)
>
> This is a long shot, but you could try using javascript. Normally, the
> last value selected before the View Report button is selected is shown
> in an expression based on a parameter. Sorry that I could not be of
> further assistance.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On Dec 12, 10:21 pm, Peetaah <Peet...@.discussions.microsoft.com>
wrote:
> Thanks, that is what I was thinking too-- just hoping there was a secret call
> I could make to get the current value :)
> "EMartinez" wrote:
> > On Dec 12, 5:25 pm, Peetaah <Peet...@.discussions.microsoft.com> wrote:
> > > So it looks like when you add an expression which grabs a parameter value
> > > (Parameters!SRNumber.Value), it grabs the data from when the "View Report"
> > > button was originally pressed. My goal is to allow someone to punch in new
> > > data into a parameter and without having to re-run the report, be able to use
> > > that for an action. Any ideas? Example of what I am attempting to do below.
> > > = "mailto:" + Fields!SOURCE_EMAIL.Value + "?subject=SR #"
> > > +Parameters!SRNumber.Value + " has been assigned to you"...
> > > (SRNumber is a blank parameter which isn't actually used in any of my
> > > queries, but rather just there for this purpose)
> > This is a long shot, but you could try using javascript. Normally, the
> > last value selected before the View Report button is selected is shown
> > in an expression based on a parameter. Sorry that I could not be of
> > further assistance.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
You're welcome. Sorry that I could not be of greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Global access for Everyone Group

I have been tasked with taking over a Crystal XI Enterprise installation that uses the Everyone group to grant view access to all objects/folders. Where do I turn this off globally? In other words, I want the Everyone group to have no access rights to anything - so I can use created groups to administer access rights for obejcts/folders. Thanks for any help in advance.I have found the correct place for this under Settings. Thanks for your patience.

GK-Permissions on all database objects

Hi,
I'm looking for a way to view / report all permissions on all database objec
ts ( logins / roles / users and all object permissions ( select, delete, upd
ate execute etc. ) ).
If you can help please do...
Gkramer
The NetherlandsHi,
Please refer books online for the system procedure,
sp_helprotect
Thanks
Hri
MCDBA
"Gkramer" <anonymous@.discussions.microsoft.com> wrote in message
news:8626D21C-7192-4313-8CC1-0EDE6CCC2B43@.microsoft.com...
> Hi,
> I'm looking for a way to view / report all permissions on all database
objects ( logins / roles / users and all object permissions ( select,
delete, update execute etc. ) ).
> If you can help please do...
> Gkramer
> The Netherlands

Giving permission to view CERTAIN records

I've been looking through permissions to see if it's possible to grant a user permission to see only certain records from a table.

For example:

I'm granting users to view records in the table 'Sales', but I only want the users to see their respective data.

User A should only be able to see Sales where Sales.Location = 1

User B should only be able to see Sales where Sales.Location = 2

... and so on.

I believe I've read this is possible, but in looking through the permissions you can define, I've only found where you can limit the columns a user sees, not records.

Anyone have any ideas or did I just remember something wrong?

This cannot be done through permissions today. This topic is known as row-level-security. You can search this forum for old threads on this topic. The following whitepaper may also be useful to you:

http://www.microsoft.com/technet/prodtechnol/sql/2005/multisec.mspx

For your example, one solution could be to use two views that filter the sales data and grant permission on each view to a different user. Users should not be able to access data directly, only through the views they have access to.

Thanks
Laurentiu

|||

Great! This sounds both useful and preferable. Too bad that when the project I'm working on gets under way, they'll likely be a couple hundred different users. Good thing I'm not developing it!

Thanks!

|||

You don't really need a view for each user, you'll need one for each type of user access you need to grant.

Also, the toolkit used by the whitepaper I mentioned is available at http://blogs.msdn.com/publicsector/archive/2006/11/16/sql-server-2005-label-security-toolkit.aspx.

Thanks
Laurentiu

|||

You might try something like this:

create table Account(AccountName sysname, Location int)

insert Account values('mydomain\myuser', 1)

create table Sales(SalesData varchar(20), Location int)

insert Sales values ('xyz', 1)

go

create view vwSales as

select Sales.* from Sales join Account on Sales.Location = Account.Location

where Account.AccountName = suser_sname()

This assumes that the user is logged into SQL Server with their Windows user account. If they are using a service account or a non-Windows account, this won't work.

Ron Rice

|||

In our Marketing Information System, end-users see only views, corresponding to their department and position.

Who needs row level security when you can create views...

Giving permission to view CERTAIN records

I've been looking through permissions to see if it's possible to grant a user permission to see only certain records from a table.

For example:

I'm granting users to view records in the table 'Sales', but I only want the users to see their respective data.

User A should only be able to see Sales where Sales.Location = 1

User B should only be able to see Sales where Sales.Location = 2

... and so on.

I believe I've read this is possible, but in looking through the permissions you can define, I've only found where you can limit the columns a user sees, not records.

Anyone have any ideas or did I just remember something wrong?

This cannot be done through permissions today. This topic is known as row-level-security. You can search this forum for old threads on this topic. The following whitepaper may also be useful to you:

http://www.microsoft.com/technet/prodtechnol/sql/2005/multisec.mspx

For your example, one solution could be to use two views that filter the sales data and grant permission on each view to a different user. Users should not be able to access data directly, only through the views they have access to.

Thanks
Laurentiu

|||

Great! This sounds both useful and preferable. Too bad that when the project I'm working on gets under way, they'll likely be a couple hundred different users. Good thing I'm not developing it!

Thanks!

|||

You don't really need a view for each user, you'll need one for each type of user access you need to grant.

Also, the toolkit used by the whitepaper I mentioned is available at http://blogs.msdn.com/publicsector/archive/2006/11/16/sql-server-2005-label-security-toolkit.aspx.

Thanks
Laurentiu

|||

You might try something like this:

create table Account(AccountName sysname, Location int)

insert Account values('mydomain\myuser', 1)

create table Sales(SalesData varchar(20), Location int)

insert Sales values ('xyz', 1)

go

create view vwSales as

select Sales.* from Sales join Account on Sales.Location = Account.Location

where Account.AccountName = suser_sname()

This assumes that the user is logged into SQL Server with their Windows user account. If they are using a service account or a non-Windows account, this won't work.

Ron Rice

|||

In our Marketing Information System, end-users see only views, corresponding to their department and position.

Who needs row level security when you can create views...