Query Privileges

Hi NG,
How do I select the privileges for a particular user in SQL Server?
sp_helpuser doesn't help. I need to get the list of tables, views etc. that
a particular user have access.
************************************************
This is how I do it in Oracle.
select * from DBA_TAB_PRIVS where grantee = username
************************************************
Thank you in advance.
Hi
I think Aaron wrote this script
CREATE FUNCTION dbo.RoleCheckUser
(
@.UserName sysname,
@.RoleName sysname
)
RETURNS BIT
AS
BEGIN
DECLARE @.RetVal BIT
SET @.RetVal = 0
SELECT @.RetVal = 1
WHERE EXISTS
(
SELECT *
FROM sysmembers membs
JOIN sysusers users on membs.memberuid = users.uid
JOIN sysusers groups on membs.groupuid = groups.uid
WHERE
users.name = @.UserName
AND groups.name = @.RoleName
)
RETURN @.RetVal
END
GO
-- Syntax to use the created function
SELECT dbo.RoleCheckUser('dbo', 'db_owner')
GO
"Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
news:OcAvzDeJIHA.3400@.TK2MSFTNGP03.phx.gbl...
> Hi NG,
> How do I select the privileges for a particular user in SQL Server?
> sp_helpuser doesn't help. I need to get the list of tables, views etc.
> that a particular user have access.
> ************************************************
> This is how I do it in Oracle.
> select * from DBA_TAB_PRIVS where grantee = username
> ************************************************
> Thank you in advance.
>
|||Try executing EXEC sp_helprotect under the required database.
Manu
"Praetorian Guard" wrote:

> Hi NG,
> How do I select the privileges for a particular user in SQL Server?
> sp_helpuser doesn't help. I need to get the list of tables, views etc. that
> a particular user have access.
> ************************************************
> This is how I do it in Oracle.
> select * from DBA_TAB_PRIVS where grantee = username
> ************************************************
> Thank you in advance.
>
>
|||Praetorian Guard (praetorian@.gatekeeper.com) writes:
> How do I select the privileges for a particular user in SQL Server?
> sp_helpuser doesn't help. I need to get the list of tables, views etc.
> that a particular user have access.
> ************************************************
> This is how I do it in Oracle.
> select * from DBA_TAB_PRIVS where grantee = username
> ************************************************
If you are on SQL 2005, have a look on fn_my_permissions and
Has_Perms_By_Name. fn_my_permissons would have been really useful,
had it only accepted a column for the first parameter, but it appears
to only accept strings and variables, so you would have to run a cursor
over it. Has_Perms_By_Name takes a column so it can be used a query,
but you can only check one permission at time.
Both presume that you impersonate the user in question with EXECUTE AS.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Query Privileges

Hi NG,
How do I select the privileges for a particular user in SQL Server?
sp_helpuser doesn't help. I need to get the list of tables, views etc. that
a particular user have access.
****************************************
********
This is how I do it in Oracle.
select * from DBA_TAB_PRIVS where grantee = username
****************************************
********
Thank you in advance.Hi
I think Aaron wrote this script
CREATE FUNCTION dbo.RoleCheckUser
(
@.UserName sysname,
@.RoleName sysname
)
RETURNS BIT
AS
BEGIN
DECLARE @.RetVal BIT
SET @.RetVal = 0
SELECT @.RetVal = 1
WHERE EXISTS
(
SELECT *
FROM sysmembers membs
JOIN sysusers users on membs.memberuid = users.uid
JOIN sysusers groups on membs.groupuid = groups.uid
WHERE
users.name = @.UserName
AND groups.name = @.RoleName
)
RETURN @.RetVal
END
GO
-- Syntax to use the created function
SELECT dbo.RoleCheckUser('dbo', 'db_owner')
GO
"Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
news:OcAvzDeJIHA.3400@.TK2MSFTNGP03.phx.gbl...
> Hi NG,
> How do I select the privileges for a particular user in SQL Server?
> sp_helpuser doesn't help. I need to get the list of tables, views etc.
> that a particular user have access.
> ****************************************
********
> This is how I do it in Oracle.
> select * from DBA_TAB_PRIVS where grantee = username
> ****************************************
********
> Thank you in advance.
>|||Try executing EXEC sp_helprotect under the required database.
Manu
"Praetorian Guard" wrote:

> Hi NG,
> How do I select the privileges for a particular user in SQL Server?
> sp_helpuser doesn't help. I need to get the list of tables, views etc. tha
t
> a particular user have access.
> ****************************************
********
> This is how I do it in Oracle.
> select * from DBA_TAB_PRIVS where grantee = username
> ****************************************
********
> Thank you in advance.
>
>|||Praetorian Guard (praetorian@.gatekeeper.com) writes:
> How do I select the privileges for a particular user in SQL Server?
> sp_helpuser doesn't help. I need to get the list of tables, views etc.
> that a particular user have access.
> ****************************************
********
> This is how I do it in Oracle.
> select * from DBA_TAB_PRIVS where grantee = username
> ****************************************
********
If you are on SQL 2005, have a look on fn_my_permissions and
Has_Perms_By_Name. fn_my_permissons would have been really useful,
had it only accepted a column for the first parameter, but it appears
to only accept strings and variables, so you would have to run a cursor
over it. Has_Perms_By_Name takes a column so it can be used a query,
but you can only check one permission at time.
Both presume that you impersonate the user in question with EXECUTE AS.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Query Privileges

Hi NG,
How do I select the privileges for a particular user in SQL Server?
sp_helpuser doesn't help. I need to get the list of tables, views etc. that
a particular user have access.
************************************************
This is how I do it in Oracle.
select * from DBA_TAB_PRIVS where grantee = username
************************************************
Thank you in advance.Hi
I think Aaron wrote this script
CREATE FUNCTION dbo.RoleCheckUser
(
@.UserName sysname,
@.RoleName sysname
)
RETURNS BIT
AS
BEGIN
DECLARE @.RetVal BIT
SET @.RetVal = 0
SELECT @.RetVal = 1
WHERE EXISTS
(
SELECT *
FROM sysmembers membs
JOIN sysusers users on membs.memberuid = users.uid
JOIN sysusers groups on membs.groupuid = groups.uid
WHERE
users.name = @.UserName
AND groups.name = @.RoleName
)
RETURN @.RetVal
END
GO
-- Syntax to use the created function
SELECT dbo.RoleCheckUser('dbo', 'db_owner')
GO
"Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
news:OcAvzDeJIHA.3400@.TK2MSFTNGP03.phx.gbl...
> Hi NG,
> How do I select the privileges for a particular user in SQL Server?
> sp_helpuser doesn't help. I need to get the list of tables, views etc.
> that a particular user have access.
> ************************************************
> This is how I do it in Oracle.
> select * from DBA_TAB_PRIVS where grantee = username
> ************************************************
> Thank you in advance.
>|||Try executing EXEC sp_helprotect under the required database.
Manu
"Praetorian Guard" wrote:
> Hi NG,
> How do I select the privileges for a particular user in SQL Server?
> sp_helpuser doesn't help. I need to get the list of tables, views etc. that
> a particular user have access.
> ************************************************
> This is how I do it in Oracle.
> select * from DBA_TAB_PRIVS where grantee = username
> ************************************************
> Thank you in advance.
>
>|||Praetorian Guard (praetorian@.gatekeeper.com) writes:
> How do I select the privileges for a particular user in SQL Server?
> sp_helpuser doesn't help. I need to get the list of tables, views etc.
> that a particular user have access.
> ************************************************
> This is how I do it in Oracle.
> select * from DBA_TAB_PRIVS where grantee = username
> ************************************************
If you are on SQL 2005, have a look on fn_my_permissions and
Has_Perms_By_Name. fn_my_permissons would have been really useful,
had it only accepted a column for the first parameter, but it appears
to only accept strings and variables, so you would have to run a cursor
over it. Has_Perms_By_Name takes a column so it can be used a query,
but you can only check one permission at time.
Both presume that you impersonate the user in question with EXECUTE AS.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspxsql

Query plan re-use on views?

Here's the setup:

Client database has a complex view with eight nested subqueries used to return "dashboard" information. The application code uses NHibernate to call and filter the view with three parameters, one of which is the CustomerID.

A certain customer, (the biggest client), has more than ten times the number of records of the next largest customer.

Occasionally, the database reaches a state where when this particular customer tries to run the dashboard view, the application times out.

If I open up the view and re-save it, all is well again for a few days.

What gives?

Views are supposedly not pre-compiled, though I know that 2000 stores bits and pieces of query plans.

Any ideas on what causes this and what to do about it?are you experiencing large amounts of new data on a regular basis throwing off statistics and fragmenting your indices. just cuz they ain't precompiled don;t mean that they do not use that stuff right?|||No. Loaded a ton of historical data when the app was first installed, but inserts have been slow and steady since then.

Query performance on paritioned views with check constraints

Hi,

I have come across this problem with SQL server both on 2000 and 2005. I am stating an example here.

I have two partitioned tables and a view on top of both tables as below:

create table [dbo].[Table_1]

(

[TableID] INTEGER PRIMARY KEY NONCLUSTERED

CHECK NOT FOR REPLICATION ([TableID] BETWEEN 1 AND 999),

[AnyOtherColumn] int NOT NULL ,

) ON [Primary]

GO

create table [dbo].[Table_2]

(

[TableID] INTEGER PRIMARY KEY NONCLUSTERED

CHECK NOT FOR REPLICATION ([TableID] BETWEEN 1000 AND 1999),

[AnyOtherColumn] int NOT NULL ,

) ON [Primary]

GO

create view TableView

as

select * from Table_1

union all

select * from Table_2

GO

Note the NOT FOR REPLICATION clause on the check constraint on the TableID column.

I then ran the query execution plan for the following query on both SQL server 2000 and 2005.

select * from TableView where TableID = 10

On both the versions the execution plan shows and Index seek on both the tables in the view. This means that my partitioning is not working. If I remove the primary key constraint from the TableID column, the same query on the view shows a table scan on all the underlying tables. This is even worse.

Next, create the same tables and views again, now without the NOT FOR REPLICATION clause on the check constraint as show below:

create table [dbo].[Table_1]

(

[TableID] INTEGER PRIMARY KEY NONCLUSTERED

CHECK ([TableID] BETWEEN 1 AND 999),

[AnyOtherColumn] int NOT NULL ,

) ON [Primary]

GO

create table [dbo].[Table_2]

(

[TableID] INTEGER PRIMARY KEY NONCLUSTERED

CHECK ([TableID] BETWEEN 1000 AND 1999),

[AnyOtherColumn] int NOT NULL ,

) ON [Primary]

GO

create view TableView

as

select * from Table_1

union all

select * from Table_2

GO

Now run the query execution plan for the same query again.

select * from TableView where TableID = 10

This time you would see that it does an index scan only on the first parititon table. This time it proves that the partitioning works.

I would like to know why does the NOT FOR REPLICATION clause in the check constraint make such a huge difference?

Is it a bug in SQL server?

Or am I missing any thing?

Any help appreciated.

Thanks

NOT FOR REPLICATION means that your check constraint is not enforced when a replication agent performs insert, update, or delete operations. This means that the system cannot assume that the data in the column actually meets that constraint and so it has to scan both tables.

Removing the option means that it knows that data in the column meets the constraint and only has to scan the table whose constraint contains that value.

|||

Thanks,

I understand why we use NOT FOR REPLICATION clauses. But replication was not is the scope of this query. I meant to know the performance impact for queries on the partitioned view, with and without the NOT FOR REPLICATION clause on check constraint in the underlying tables.

for more detail, I have mentioned the problem here

http://vikramkamath.wordpress.com/2007/03/21/partitioned-views-check-constraints-with-not-for-replication-clause/

Cheers

Query Not Collating correctly

I have restored an SQL 2000 database in to 2005 Standard edition.

I have several views that are displaying incorrect results though when viewed in SQL Management Studio, and hence back to Excel Word etc....

The view is as follows:

SELECT TOP (100) PERCENT DATEPART(mm, ARV_date) AS Month, COUNT(*) AS Count, SUM(DATEDIFF(mi, ARV_date, RES_date)) AS [Minutes Unresolved],
MAX(DATEDIFF(mi, ARV_date, RES_date)) AS [Max Dur(min)]
FROM dbo.ps_Remedy_Data
WHERE (DATEPART(yy, ARV_date) = DATEPART(yy, GETDATE())) AND (RMDY_Priority = 'Urgent')
GROUP BY DATEPART(mm, ARV_date), RMDY_Priority
ORDER BY Month

When I execute it from Word or Excel the returned data is NOT in Month order...

When I right click the view and say Open View it returns the same unsorted data...

When I right click the view and say MODIFY and do nothing else except EXECUTE the SQL the returned data is in the correct sequence.

This is extremely annoying and can only be described as a bug.

This is a new server running WIN 2003 SP1, with SQL Server 2005 SP1.

Thoughts appreciated, many thanks in advance

regards

Order in views make no sense, though the order by clause is only used within the use of the "TOP Something" clause to ensure that the data is ordered first to give the right data back, using TOP 100 Precent won′t have any effect on that. There is a new blog entry from Kimberly about that, perhaps you shoul have a look on that.

http://www.sqlskills.com/blogs/kimberly/PermaLink.aspx?guid=79de45cb-2da6-4510-ae43-7e5e42e4c02e

The best way would be to treat the view as a column definition and do the order on the view.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

Query no returning desired resluts, HELP

I have 3tables/views I need to pull into one query for a results set for a Gridview table.

2 views, 1 table.

1st view, vw_ORG has fields, ORGID, ORGNAME

2nd view, vw_Staff, has fields, ORGID, STAFFID, FIRSTNAME, LASTNAME, MIDDLENAME, PREFIX, DEGREE

1st table : tbl_Data, has fields, ORGID, LASTMODIFIEDBY (which is a fk, pk in vw_Staff), LASTMODIFIED (date time ).

Basically my results need to have the ORGID, ORGNAME, LASTMODIFIED DATE and LASTMODIFIEDBY.

Would look like this...

ORGID ORGNAME LASTMODIFIED DATE LASTMODIFIEDBY
1 Science 10/10/2006 9:42 a.m. John P. Smith
22 Mathmatic 10/01/2006 9:15 p.m. Leslie Stahl
95 Football 5/01/2006 12:15 a.m. Terrell Owens

I have gotten results that displays the lastmodified date use MAX but can't figure out how to pull in the names from vw_Staff

Here is that part if you want to use as a starting base.

Select DISTINCT b.orgid, b.OrgName,
(SELECT Max(d.lastmodified)FROM tbl_Data d WHERE d.orgid = b.orgid) AS modifiedDate
From vw_ORGS b
ORDER BY ORGID

I've been banging my head against a wall for 2 days now and am desperatly needing some resolution before the voices in my head start telling me to harm myself j/k.No one can help with this... ?|||select o.ORGID
, o.ORGNAME
, d.LASTMODIFIED
, s.FIRSTNAME
from vw_ORG as o
inner
join tbl_Data as d
on d.ORGID = o.ORGID
inner
join vw_Staff as s
on s.STAFFID = d.LASTMODIFIEDBY|||To many results from your code. but the attmept is most appreciated.

Basically it has to be a MAX date from when the ORG was last modified. Only one row of data per ORG.|||so the tbl_Data table has multiple "last modified" rows per ORGID?select o.ORGID
, o.ORGNAME
, d.LASTMODIFIED
, s.FIRSTNAME
from vw_ORG as o
inner
join tbl_Data as d
on d.ORGID = o.ORGID
and d.LASTMODIFIED =
( select max(LASTMODIFIED)
from tbl_Data
where ORGID = o.ORGID )
inner
join vw_Staff as s
on s.STAFFID = d.LASTMODIFIEDBY|||Yes, that is corrrect.. I was able to get the quasi results needed by using SELECT DISTINCT?

But another issue is that I need to retireve all ORGS weather or not the have a last modified date or not. Is there a IS NULL function or something that can be used to with the select max(LASTMODIFIED) to do this?

Thanks.|||change INNER to LEFT OUTER in two places