Determining windows grouop membership of a SQL login

Hello,
I'm trying to determine the windows group membership of a SQL login I've
created from a Windows login. Specifically, I'm trying to determine if the
login is in BUILTIN\Administrators or otherwise has admin permissions. For
example,
CREATE LOGIN <login> FROM WINDOWS WITH DEFAULT_DATABASE <database>
USE <database>
CREATE USER <login>
The CREATE USER call will fail if the login has admin privs because they
already have access under 'dbo'. So how do I go about determining the
membership of this login? sp_helplogins, sp_helpuser, sp_helpgroup don't
seem to provide the information I need.
Suggestions?
KeithKeith,
You could check with: is_srvrolemember ('sysadmin','Domain\Loginame')
RLF
"Keith" <keith@.alh.com> wrote in message
news:uBrsCyq2HHA.2752@.TK2MSFTNGP06.phx.gbl...
> Hello,
> I'm trying to determine the windows group membership of a SQL login I've
> created from a Windows login. Specifically, I'm trying to determine if
> the login is in BUILTIN\Administrators or otherwise has admin permissions.
> For example,
> CREATE LOGIN <login> FROM WINDOWS WITH DEFAULT_DATABASE <database>
> USE <database>
> CREATE USER <login>
> The CREATE USER call will fail if the login has admin privs because they
> already have access under 'dbo'. So how do I go about determining the
> membership of this login? sp_helplogins, sp_helpuser, sp_helpgroup don't
> seem to provide the information I need.
> Suggestions?
> Keith
>|||Thanks Russell! That seems to do the trick!
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:O5C3HQr2HHA.5796@.TK2MSFTNGP05.phx.gbl...
> Keith,
> You could check with: is_srvrolemember ('sysadmin','Domain\Loginame')
> RLF
> "Keith" <keith@.alh.com> wrote in message
> news:uBrsCyq2HHA.2752@.TK2MSFTNGP06.phx.gbl...
>

determining who is logged into database server

Hi,
There used to be a command in mssql 6.5 that would tell
return the login names of users who had the database open,
ie, they were still in the app that connects to the db. I
think the command was something like @.@.who. Is there and
equivalent command in SQL 7 and 2k?
Also, is it possible to run sql 6.5 on a win2k server? It
keeps telling me the app is not loaded when I try to run
the service pack.
Thanks,
MikeI think you are looking for the system stored procedures
sp_who and sp_who2.
-Sue
On Mon, 3 Nov 2003 18:29:51 -0800, "Mike Stefani"
<anonymous@.discussions.microsoft.com> wrote:
>Hi,
>There used to be a command in mssql 6.5 that would tell
>return the login names of users who had the database open,
>ie, they were still in the app that connects to the db. I
>think the command was something like @.@.who. Is there and
>equivalent command in SQL 7 and 2k?
>Also, is it possible to run sql 6.5 on a win2k server? It
>keeps telling me the app is not loaded when I try to run
>the service pack.
>Thanks,
>Mike|||Hi,
I think you are referring to SP_WHO system stored procedure. You can use the
same procedure in SQL 7.0 and SQL 2k from Query analyzer to identify
all the current processes (Users) connected to SQL server.
You can defenetely run SQL 6.5 is Win2k server , but as you said there is
lots of problems. Microsoft recommends not to run SQL 6.5 in Win2k.
Thanks
Hari
MCDBA
"Mike Stefani" <anonymous@.discussions.microsoft.com> wrote in message
news:0b1a01c3a27b$862eff90$a101280a@.phx.gbl...
> Hi,
> There used to be a command in mssql 6.5 that would tell
> return the login names of users who had the database open,
> ie, they were still in the app that connects to the db. I
> think the command was something like @.@.who. Is there and
> equivalent command in SQL 7 and 2k?
> Also, is it possible to run sql 6.5 on a win2k server? It
> keeps telling me the app is not loaded when I try to run
> the service pack.
> Thanks,
> Mike|||its sp_who
--
Shaju Thomas
"Mike Stefani" <anonymous@.discussions.microsoft.com> wrote in message
news:0b1a01c3a27b$862eff90$a101280a@.phx.gbl...
> Hi,
> There used to be a command in mssql 6.5 that would tell
> return the login names of users who had the database open,
> ie, they were still in the app that connects to the db. I
> think the command was something like @.@.who. Is there and
> equivalent command in SQL 7 and 2k?
> Also, is it possible to run sql 6.5 on a win2k server? It
> keeps telling me the app is not loaded when I try to run
> the service pack.
> Thanks,
> Mike

Determining which version of SQL is running

hi peter... i have a question about, how i can see if my sql server is the version 2005 sp2 and, what is the diference with server and server agent... i've checked the updates and the machine says i have up to date... but i dont know witch is.

thank you for your help

There are several ways to determine which version you are running. You can open a query window and type in "SELECT @.@.VERSION". This will return your version information. You can also go to Add/Remove Programs, click on your SQL 2005 entry, click on Remove and then Report. This will display a nice summary of what components/versions you are running on your machine.

Thanks,
Sam Lester (MSFT)

As for SQL Agent, it is a job scheduling service for SQL 2005. You can read about it here:

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

Determining which version of SQL is running

hi peter... i have a question about, how i can see if my sql server is the version 2005 sp2 and, what is the diference with server and server agent... i've checked the updates and the machine says i have up to date... but i dont know witch is.

thank you for your help

There are several ways to determine which version you are running. You can open a query window and type in "SELECT @.@.VERSION". This will return your version information. You can also go to Add/Remove Programs, click on your SQL 2005 entry, click on Remove and then Report. This will display a nice summary of what components/versions you are running on your machine.

Thanks,
Sam Lester (MSFT)

As for SQL Agent, it is a job scheduling service for SQL 2005. You can read about it here:

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

DETERMINING WHICH HIERARCHY OF A DIMENSION IS SELECTED

I am trying to use MDX to dynamically determine which hierarchy of the
time dimension is selected. I have a calculated member which is using
the last non-empty descendant of the [Time].[Calendar] dimension, but
I want it to work even if Fiscal is selected instead of Calendar.
Here is the code

SUM(Tail(Filter(Descendants([Time].[Calendar].CurrentMember, [Calendar
Date]), isEmpty([Measures].[Employee Count]) = False), 1),
[Measures].[Employee Count])

What can I put in place of [Time].[Calendar].CurrentMember to take the
current hierarchy of the time dimension instead of the Calendar
hierarchy? Thanks!"Sara" <sara2thebradley@.yahoo.com> wrote in message
news:ce695524.0402030731.75f8ea41@.posting.google.c om...
> I am trying to use MDX to dynamically determine which hierarchy of the
> time dimension is selected. I have a calculated member which is using
> the last non-empty descendant of the [Time].[Calendar] dimension, but
> I want it to work even if Fiscal is selected instead of Calendar.
> Here is the code
> SUM(Tail(Filter(Descendants([Time].[Calendar].CurrentMember, [Calendar
> Date]), isEmpty([Measures].[Employee Count]) = False), 1),
> [Measures].[Employee Count])
>
> What can I put in place of [Time].[Calendar].CurrentMember to take the
> current hierarchy of the time dimension instead of the Calendar
> hierarchy? Thanks!

You'll probably get a better response by posting this in
microsoft.public.sqlserver.olap, as it's quite a specialized area.

Simon

Determining which email failed.

I have a data driven subscription which delivers multiple reports to
multiple subscribers.
For the most part, it works great.
But, sometimes there is a failure in the delivery.
Ie 10 reports were to be delivered to XXX@.xx.com.
It says that 8 were successful, and 2 failed.
Unfortunately, I cannot seem to determine which of the 10 failed.
This causes me no end of grief, as I cannot know which to resend.
I've tried looking at the log files to no avail.
Is there any way to determine which ones failed?As far as I understand the most straightforward method would be examination
the mail server logs.
Still quite a challenge.
"Zresse" <zresse@.yahoo.com> wrote in message
news:1128518677.725131.233550@.f14g2000cwb.googlegroups.com...
>I have a data driven subscription which delivers multiple reports to
> multiple subscribers.
> For the most part, it works great.
> But, sometimes there is a failure in the delivery.
> Ie 10 reports were to be delivered to XXX@.xx.com.
> It says that 8 were successful, and 2 failed.
> Unfortunately, I cannot seem to determine which of the 10 failed.
> This causes me no end of grief, as I cannot know which to resend.
> I've tried looking at the log files to no avail.
> Is there any way to determine which ones failed?
>|||Hey, Guess who?

Determining when to send the report based on information inside the report?!?

Hello

Here is a tricky problem to which I didn't find any "good" solution:

I have a report which should be sent to the subscriber only when there is something to report. Mostly the report will be empty, because it will only report emergency-type-of-stuff. Now I have implemented the report logic twice, once in the report and once in a data driven subscription which returns a fixed email address when the report data query returns data.

Ideal solution would be one where you can decide in the report generation is it sent or not. Has anyone any ideas / solutions conserning this problem? How could you decide during the report generation that is report sent or not? Any help is highly appreciated...

Thanks!

- rusi

Well, that seems a bit interesting...one problem I see is that the report generation could be performed anytime...i.e. on a schedule, or also anytime a user interactivelly requests to view the report. So, if for example you did include logic to email the report anytime the report generation included data, would you want to get multiple emails time after time if a user kept viewing the report every 10-15 minutes or something like that?

Seems to me that your best bet would be to encapsulate the data-portion of the report (i.e. the queries/data sets) within a stored procedure that you can call from multiple places, and if the sp returns any results, fire off an email from your automated systems when you would like under controlled circumstances.

As it is, I'm not entirely sure that you can even fire off an email based on report content...maybe someone else can chime in on this part...

|||Thanks for the answer!
That is true that the report is ran every time, in this case twice a day, and it sends a report of orders that have not been confirmed. But there is no need to send the report if there are no unconfirmed orders eg. nothing to report...
It is possible to solve this putting the logic in one sp, but still have to implement to logic somewhere else then in the report. I'm searching for the best solution in which the report includes all the logic.
|||This is a fairly common request. Your current solution is the best work around that we have right now. We are looking at the possibility of adding this feature in future versions.

Determining what to input as server

I'm trying to connect to a sql database, but I don't know what myserver is in the following code.
Dim strConn As String = "server=myserver;database=Northwind"

I can't get the code to link up with my Northwind database.

I'm running everything locally if that helps.

Thanks!

JonYou mean what to use instead of "myserver"? It is the machine name on the network where the SQL Server is located. If it is on the same machine as where you are running the ASP.NET app, you can either use the machine name or "(local)"

Don|||Oh, and you can also use an IP address (such as 10.12.15.16) if you have it, or an internet address (theserver.mydomain.com).

Don

Determining what stored procedures are used

Hi,
We have a large database with over 1000 stored procedures,
we need to determine when last these procedures where used
so we can remove and redundant/unused procedures. Is
there any way to do this using SQL Server 2000?
Thanks,
StephenStephen, the answer is simple: yoes, you should use the Profiler. Check this
tool in Books OnLine, it is very powerful!
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Stephen Young" <stephen_c_young@.hotmail.com> wrote in message
news:1af101c3737d$540f7030$a301280a@.phx.gbl...
> Hi,
> We have a large database with over 1000 stored procedures,
> we need to determine when last these procedures where used
> so we can remove and redundant/unused procedures. Is
> there any way to do this using SQL Server 2000?
> Thanks,
> Stephen|||Thanks, will give it a shot.
>--Original Message--
>Stephen, the answer is simple: yoes, you should use the
Profiler. Check this
>tool in Books OnLine, it is very powerful!
>--
>Dejan Sarka, SQL Server MVP
>FAQ from Neil & others at: http://www.sqlserverfaq.com
>Please reply only to the newsgroups.
>PASS - the definitive, global community
>for SQL Server professionals - http://www.sqlpass.org
>"Stephen Young" <stephen_c_young@.hotmail.com> wrote in
message
>news:1af101c3737d$540f7030$a301280a@.phx.gbl...
>> Hi,
>> We have a large database with over 1000 stored
procedures,
>> we need to determine when last these procedures where
used
>> so we can remove and redundant/unused procedures. Is
>> there any way to do this using SQL Server 2000?
>> Thanks,
>> Stephen
>
>.
>

Determining what columns are all Null

Does anyone have a suggestion for how to efficiently determine what columns in a table are NULL for all rows? - Shean

Hi,

I wrote a procedure for that:

ALTER PROCEDURE spDetermineNullColumns

(

@.TableName SYSNAME

)

AS

BEGIN

DECLARE @.ROWCOUNT INT

DECLARE @.I INT

DECLARE @.HIT INT

DECLARE @.sql NVARCHAR(2000)

SELECT IDENTITY(INT,1,1) AS IDCol,COLUMN_NAME

INTO #NullableColumns

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = @.TableName AND IS_NULLABLE = 'YES'

SET @.ROWCOUNT = @.@.ROWCOUNT

SET @.I = 1

WHILE @.I <= @.ROWCOUNT

BEGIN

SET @.Hit = 0

SELECT @.sql = N'SELECT @.Hit = 1 '+ ' FROM ' + @.TableNAME +

' WHERE ' + COLUMN_NAME + ' IS NOT NULL'

FROM #NullableColumns WHERE IDCol = @.I

PRINT @.SQL

EXEC sp_executesql

@.query = @.sql,

@.params = N'@.Hit INT OUTPUT',

@.Hit = @.Hit OUTPUT

/* This here can be customized as you want to

IF @.Hit = 0

SELECT COLUMN_NAME + ' is completly NULL.'

FROM #NullableColumns WHERE IDCol = @.I

SET @.I = @.I +1

*/

END

END

GO

spDetermineNullColumns 'Categorie'

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||DId that help Sean ?|||

You could do something like below:

select min(case when col1 is not null then 0 else 1 end) as col1_is_all_null

, min(case when col2 is not null then 0 else 1 end) as col2_is_all_null

...

from tbl

Determining what causes tempdb to autogrow

I have a SQL server with over 15 user databases on it. Tempdb autogrows to
the point that it monopolizes all available disk space (I just recently
shrank it from 36 GB to free up space). Does anyone have any tips on how to
determine what process are causing so much space usage for my tempdb? It is
perplexing...I used sysmon to track when log usage increases and I try to
correlate that with profile traces in which I capture 'text like '%#%'.
This is not the best way to do this so I was wondering if anyone had any
suggestions.
Rod,
Use perfmonitor.
Performance object - "SQLServer: Access methods"
Counters - Workfiles Created/sec,Worktables created/sec,Worktables From
cache Ratio and also anything related.
Performance object - "SQLServer:databases"
Counters - Active transactions, datafile size and also anything related.
Here is some related info:
'TechNet Support WebCast: Administering and maintaining the tempdb system
database in Microsoft SQL Server'
http://support.microsoft.com/default...&Product=sql2k
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"Rod Bautista" <rod.bautista@.adam-us.com> wrote in message
news:uLrNl#GOEHA.2716@.tk2msftngp13.phx.gbl...
> I have a SQL server with over 15 user databases on it. Tempdb autogrows
to
> the point that it monopolizes all available disk space (I just recently
> shrank it from 36 GB to free up space). Does anyone have any tips on how
to
> determine what process are causing so much space usage for my tempdb? It
is
> perplexing...I used sysmon to track when log usage increases and I try to
> correlate that with profile traces in which I capture 'text like '%#%'.
> This is not the best way to do this so I was wondering if anyone had any
> suggestions.
>
|||Some information here as well
http://www.aspfaq.com/2446
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Rod Bautista" <rod.bautista@.adam-us.com> wrote in message
news:uLrNl%23GOEHA.2716@.tk2msftngp13.phx.gbl...
>I have a SQL server with over 15 user databases on it. Tempdb autogrows to
> the point that it monopolizes all available disk space (I just recently
> shrank it from 36 GB to free up space). Does anyone have any tips on how
> to
> determine what process are causing so much space usage for my tempdb? It
> is
> perplexing...I used sysmon to track when log usage increases and I try to
> correlate that with profile traces in which I capture 'text like '%#%'.
> This is not the best way to do this so I was wondering if anyone had any
> suggestions.
>

Determining what causes tempdb to autogrow

I have a SQL server with over 15 user databases on it. Tempdb autogrows to
the point that it monopolizes all available disk space (I just recently
shrank it from 36 GB to free up space). Does anyone have any tips on how to
determine what process are causing so much space usage for my tempdb? It is
perplexing...I used sysmon to track when log usage increases and I try to
correlate that with profile traces in which I capture 'text like '%#%'.
This is not the best way to do this so I was wondering if anyone had any
suggestions.Rod,
Use perfmonitor.
Performance object - "SQLServer: Access methods"
Counters - Workfiles Created/sec,Worktables created/sec,Worktables From
cache Ratio and also anything related.
Performance object - "SQLServer:databases"
Counters - Active transactions, datafile size and also anything related.
Here is some related info:
'TechNet Support WebCast: Administering and maintaining the tempdb system
database in Microsoft SQL Server'
http://support.microsoft.com/default.aspx?scid=kb;en-us;834846&Product=sql2k
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Rod Bautista" <rod.bautista@.adam-us.com> wrote in message
news:uLrNl#GOEHA.2716@.tk2msftngp13.phx.gbl...
> I have a SQL server with over 15 user databases on it. Tempdb autogrows
to
> the point that it monopolizes all available disk space (I just recently
> shrank it from 36 GB to free up space). Does anyone have any tips on how
to
> determine what process are causing so much space usage for my tempdb? It
is
> perplexing...I used sysmon to track when log usage increases and I try to
> correlate that with profile traces in which I capture 'text like '%#%'.
> This is not the best way to do this so I was wondering if anyone had any
> suggestions.
>|||Some information here as well
http://www.aspfaq.com/2446
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Rod Bautista" <rod.bautista@.adam-us.com> wrote in message
news:uLrNl%23GOEHA.2716@.tk2msftngp13.phx.gbl...
>I have a SQL server with over 15 user databases on it. Tempdb autogrows to
> the point that it monopolizes all available disk space (I just recently
> shrank it from 36 GB to free up space). Does anyone have any tips on how
> to
> determine what process are causing so much space usage for my tempdb? It
> is
> perplexing...I used sysmon to track when log usage increases and I try to
> correlate that with profile traces in which I capture 'text like '%#%'.
> This is not the best way to do this so I was wondering if anyone had any
> suggestions.
>

Determining what causes tempdb to autogrow

I have a SQL server with over 15 user databases on it. Tempdb autogrows to
the point that it monopolizes all available disk space (I just recently
shrank it from 36 GB to free up space). Does anyone have any tips on how to
determine what process are causing so much space usage for my tempdb? It is
perplexing...I used sysmon to track when log usage increases and I try to
correlate that with profile traces in which I capture 'text like '%#%'.
This is not the best way to do this so I was wondering if anyone had any
suggestions.Rod,
Use perfmonitor.
Performance object - "SQLServer: Access methods"
Counters - Workfiles Created/sec,Worktables created/sec,Worktables From
cache Ratio and also anything related.
Performance object - "SQLServer:databases"
Counters - Active transactions, datafile size and also anything related.
Here is some related info:
'TechNet Support WebCast: Administering and maintaining the tempdb system
database in Microsoft SQL Server'
http://support.microsoft.com/defaul...6&Product=sql2k
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Rod Bautista" <rod.bautista@.adam-us.com> wrote in message
news:uLrNl#GOEHA.2716@.tk2msftngp13.phx.gbl...
> I have a SQL server with over 15 user databases on it. Tempdb autogrows
to
> the point that it monopolizes all available disk space (I just recently
> shrank it from 36 GB to free up space). Does anyone have any tips on how
to
> determine what process are causing so much space usage for my tempdb? It
is
> perplexing...I used sysmon to track when log usage increases and I try to
> correlate that with profile traces in which I capture 'text like '%#%'.
> This is not the best way to do this so I was wondering if anyone had any
> suggestions.
>|||Some information here as well
http://www.aspfaq.com/2446
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Rod Bautista" <rod.bautista@.adam-us.com> wrote in message
news:uLrNl%23GOEHA.2716@.tk2msftngp13.phx.gbl...
>I have a SQL server with over 15 user databases on it. Tempdb autogrows to
> the point that it monopolizes all available disk space (I just recently
> shrank it from 36 GB to free up space). Does anyone have any tips on how
> to
> determine what process are causing so much space usage for my tempdb? It
> is
> perplexing...I used sysmon to track when log usage increases and I try to
> correlate that with profile traces in which I capture 'text like '%#%'.
> This is not the best way to do this so I was wondering if anyone had any
> suggestions.
>

determining what are "system" objects in sp_help or system tables

Hi,

I have a few things on my databases which seem to be neither true system
objects or user objects - notably a table called 'dtproperties' (created
by Enterprise manager as I understand, relating to relationship graphing
or something) and some stored procs begining with "dt_" (some kind of
source control stuff, possible visual studio related). These show up when
I use

"exec sp_help 'databaseName'"

but not in Ent. Mgr. or in Query Analyzer's object browser, and also not
in a third party tool I use called AdeptSQL. I am wondering how those
tools know to differentiate between these types of quasi-system objects,
and my real user data. (This is for the purpose of a customized schema
generator I am writing). I'd prefer to determine this info with system
stored procs (ie sp_help, sp_helptex, sp_...etc) but will dip into the
system tables if needed.

Thanks,
DaveDave C. (spam@.spam.spam) writes:
> I have a few things on my databases which seem to be neither true system
> objects or user objects - notably a table called 'dtproperties' (created
> by Enterprise manager as I understand, relating to relationship graphing
> or something) and some stored procs begining with "dt_" (some kind of
> source control stuff, possible visual studio related). These show up when
> I use
> "exec sp_help 'databaseName'"
> but not in Ent. Mgr. or in Query Analyzer's object browser, and also not
> in a third party tool I use called AdeptSQL. I am wondering how those
> tools know to differentiate between these types of quasi-system objects,
> and my real user data. (This is for the purpose of a customized schema
> generator I am writing). I'd prefer to determine this info with system
> stored procs (ie sp_help, sp_helptex, sp_...etc) but will dip into the
> system tables if needed.

select objectproperty(object_id('tbl'), 'IsMSShipped') reveals these
funny tables.

An answer by the way, that probably can be retrieved by spying on the
various tools with the SQL Profiler.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

Determining Users permissions

While it is possible and easy to answer the question:
"Which Users Have permissions on This Table?" by right-clicking the table,
choosing properties from the menu, then Permissions from the dialog.
Is it possible to answer the question:
"What Permissions does This user have?"
Right-clicking the user and choosing properties lets us choose a tab
"Securables" which is always empty. we can see Server Roles, but not
permissions.
We are using Active Directory Groups to group users into permission-roles.
We cannot use Schemas because a lot of our users use MS Access data projects
to view their data.
There doesn't seem to be any way to determine what permissions a User has,
from the user point of view.
I'm sure we could do this withthe Server 2000 Enterprise Manager, but not
with the new 2005 Management Studio.Hi
IF PERMISSIONS()&2=2
CREATE TABLE test_table (col1 INT)
ELSE
PRINT 'ERROR: The current user cannot create a table.'
--
This example determines whether the current user can
grant the INSERT permission on the authors table to another user.
IF PERMISSIONS(OBJECT_ID('authors'))&0x80000=0x80000
PRINT 'INSERT on authors is grantable.'
ELSE
PRINT 'You may not GRANT INSERT permissions on authors.'
--http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlr
ef/ts_pa-pz_6f78.asp>
How can I retrieve a list of objects and permissions for a specified role?
----
--
In SQL Server 2005, you can use the Has_Perms_By_Name() function
(http://msdn2.microsoft.com/en-us/library/ms189802.aspx).

> For example, I would like to list all stored procedures which a role has
> execute permission for.
This is an example of usage:
SELECT o.SchemaAndName,
has_perms_by_name(o.SchemaAndName, 'OBJECT', 'EXECUTE')
FROM (SELECT name, SCHEMA_NAME(schema_id) AS [schema],
SCHEMA_NAME(schema_id)+'.'+name AS SchemaAndName
FROM sys.objects
WHERE type = 'P') AS o
"BAzz" <Barry.Freeman@.nospam.co.uk> wrote in message
news:D130E054-26BD-4AB2-85BE-F4153BB7E7DD@.microsoft.com...
> While it is possible and easy to answer the question:
> "Which Users Have permissions on This Table?" by right-clicking the table,
> choosing properties from the menu, then Permissions from the dialog.
> Is it possible to answer the question:
> "What Permissions does This user have?"
> Right-clicking the user and choosing properties lets us choose a tab
> "Securables" which is always empty. we can see Server Roles, but not
> permissions.
> We are using Active Directory Groups to group users into permission-roles.
> We cannot use Schemas because a lot of our users use MS Access data
> projects
> to view their data.
> There doesn't seem to be any way to determine what permissions a User has,
> from the user point of view.
> I'm sure we could do this withthe Server 2000 Enterprise Manager, but not
> with the new 2005 Management Studio.
>|||Ok, thats all very well for coders, but for example, in Enterprise Manager,
if someone asks me "What permissions on the database does Joe Bloggs in
Accounts have?" I just look up Joe Blogs login, right-click and choose Manag
e
Permissions. I check the box for only showing permissions explicitly defined
and there it is.
I can't DO that in Management studio.
I had a quick look through the functions in master, but the permissions ones
just seem to be concerned with the *current* user. I can see the value of
these... do the test, and if the can, then do the action.
But for a harrased admin being asked the above question... what can be done?
I don't mind cobbling up soem T-SQL to return the perms if required, but it
must be able to be run for any login, not jsut the current one.
BAzz
"Uri Dimant" wrote:

> Hi
> IF PERMISSIONS()&2=2
> CREATE TABLE test_table (col1 INT)
> ELSE
> PRINT 'ERROR: The current user cannot create a table.'
> --
> This example determines whether the current user can
> grant the INSERT permission on the authors table to another user.
> IF PERMISSIONS(OBJECT_ID('authors'))&0x80000=0x80000
> PRINT 'INSERT on authors is grantable.'
> ELSE
> PRINT 'You may not GRANT INSERT permissions on authors.'
> --http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsq
lref/ts_pa-pz_6f78.asp>
> How can I retrieve a list of objects and permissions for a specified role
?
> ----
--
> In SQL Server 2005, you can use the Has_Perms_By_Name() function
> (http://msdn2.microsoft.com/en-us/library/ms189802.aspx).
>
> This is an example of usage:
> SELECT o.SchemaAndName,
> has_perms_by_name(o.SchemaAndName, 'OBJECT', 'EXECUTE')
> FROM (SELECT name, SCHEMA_NAME(schema_id) AS [schema],
> SCHEMA_NAME(schema_id)+'.'+name AS SchemaAndName
> FROM sys.objects
> WHERE type = 'P') AS o
>
>
>
>
> "BAzz" <Barry.Freeman@.nospam.co.uk> wrote in message
> news:D130E054-26BD-4AB2-85BE-F4153BB7E7DD@.microsoft.com...
>
>|||BAzz,
Assuming that you are a system administrator for the SQL Server, you can
execute in the context of the login that you are checking. Here is a
sample:
EXECUTE AS login='MYDOMAIN\MyLogin'
SELECT name, is_member(name) FROM sys.database_principals
WHERE is_member (name) = 1
SELECT name, (permissions(object_id)) FROM sys.objects
WHERE permissions(object_id) > 0
REVERT
FWIW, RLF
"BAzz" <Barry.Freeman@.nospam.co.uk> wrote in message
news:A8D05A43-38F5-4461-8334-06B8D4FBDC99@.microsoft.com...[vbcol=seagreen]
> Ok, thats all very well for coders, but for example, in Enterprise
> Manager,
> if someone asks me "What permissions on the database does Joe Bloggs in
> Accounts have?" I just look up Joe Blogs login, right-click and choose
> Manage
> Permissions. I check the box for only showing permissions explicitly
> defined
> and there it is.
> I can't DO that in Management studio.
> I had a quick look through the functions in master, but the permissions
> ones
> just seem to be concerned with the *current* user. I can see the value of
> these... do the test, and if the can, then do the action.
> But for a harrased admin being asked the above question... what can be
> done?
> I don't mind cobbling up soem T-SQL to return the perms if required, but
> it
> must be able to be run for any login, not jsut the current one.
> BAzz
>
> "Uri Dimant" wrote:
>|||Thats a great help, thanks. the Execute as... will do the trick in a
parameterised stored proc that I can have return a formatted list of perms..
Makes me wonder, though, why the functionality was removed from the 2005
Management studio.
"Russell Fields" wrote:

> BAzz,
> Assuming that you are a system administrator for the SQL Server, you can
> execute in the context of the login that you are checking. Here is a
> sample:
> EXECUTE AS login='MYDOMAIN\MyLogin'
> SELECT name, is_member(name) FROM sys.database_principals
> WHERE is_member (name) = 1
> SELECT name, (permissions(object_id)) FROM sys.objects
> WHERE permissions(object_id) > 0
> REVERT
> FWIW, RLF
>
> "BAzz" <Barry.Freeman@.nospam.co.uk> wrote in message
> news:A8D05A43-38F5-4461-8334-06B8D4FBDC99@.microsoft.com...
>
>|||FWIW, I wonder, too. The current Studio behavior is not helpful to me
either.
RLF
"BAzz" <Barry.Freeman@.nospam.co.uk> wrote in message
news:0FF6C000-6010-4119-B1DB-E84047507080@.microsoft.com...[vbcol=seagreen]
> Thats a great help, thanks. the Execute as... will do the trick in a
> parameterised stored proc that I can have return a formatted list of
> perms..
> Makes me wonder, though, why the functionality was removed from the 2005
> Management studio.
>
> "Russell Fields" wrote:
>

Determining User Role via DMO

I am trying to find a way to check if a user belongs to a program
specific role. Previously we had been using:
// Not Exact but I think it can be followed
hr = m_pDmoSrvr->GetTrueLogin(&login);
LPSQLDMOUSER pUser = 0;
hr = m_pDmoDb->GetUserByName(login, &pUser);
hr = pUser->IsMember(exclRole, &b);
But this does not seem to work if the user is a member of an NT Group
that has been added to the exclRole Role. I may not know what Group was
used to assign the Role.
Is there a way to determine whether the connecting user belongs to a
Role? I tried getting a LPSQLDMOLOGIN and using that but it did not
seem to work.
I'd be grateful for any pointers.
OtisIt looks like your checking to see of a login name, or windows username is a
member of a database role. Logins are not members of database roles; databas
e
users are. You want to test to see if the user that their login maps to is a
member of a database role, not if their login is a member of the role.
"OBQuiet" wrote:

> I am trying to find a way to check if a user belongs to a program
> specific role. Previously we had been using:
> // Not Exact but I think it can be followed
> hr = m_pDmoSrvr->GetTrueLogin(&login);
> LPSQLDMOUSER pUser = 0;
> hr = m_pDmoDb->GetUserByName(login, &pUser);
> hr = pUser->IsMember(exclRole, &b);
> But this does not seem to work if the user is a member of an NT Group
> that has been added to the exclRole Role. I may not know what Group was
> used to assign the Role.
> Is there a way to determine whether the connecting user belongs to a
> Role? I tried getting a LPSQLDMOLOGIN and using that but it did not
> seem to work.
> I'd be grateful for any pointers.
> Otis
>|||"examnotes"
<MarkWilliams@.discussions.microsoft.com> wrote in
news:1F9B3D42-70A4-4474-87A6-7579B2AC8E0D@.microsoft.com:

> It looks like your checking to see of a login name, or windows
> username is a member of a database role. Logins are not members of
> database roles; database users are. You want to test to see if the
> user that their login maps to is a member of a database role, not if
> their login is a member of the role.
>
I believe you are correct but I have not been able to trace through the
options of the SQL-DMO objects to figure out how to manage it.
It seemed like the code I posted was getting the user object. But, at that
level, it seems to only check if the object itself is a member. It does not
check that the object(user) is a member of a group that are all members of
a role. That is what I have to figure out how to do.
Otis

Determining unique children

Hi,

I need to be able to identify if a parent has the same child names as another parent.

Have a look at my data (in pic)

Can you think of a SELECT that will count for each parent, the number of other parents including themselves with all the same children's names?

So you would get something like:

Bob | 2
Bill | 1
Glen | 2

thanks,
david.That's some interesting homework!

CREATE TABLE #patp (
parent VARCHAR(20)
, child VARCHAR(20)
)

INSERT INTO #patp (parent, child)
SELECT 'Bob', 'Jen' UNION
SELECT 'Bob', 'Jill' UNION
SELECT 'Bob', 'Mike' UNION
SELECT 'Bill', 'David' UNION
SELECT 'Bill', 'Steve' UNION
SELECT 'Glen', 'Jen' UNION
SELECT 'Glen', 'Jill' UNION
SELECT 'Glen', 'Mike'

SELECT DISTINCT a.parent
, (SELECT Count(*)
FROM (SELECT b.parent AS p1, c.parent AS p2
FROM #patp AS b
FULL JOIN #patp AS c
ON (c.child = b.child)
WHERE c.parent = a.parent
GROUP BY b.parent, c.parent
HAVING Count(*) = Count(b.parent)
AND Count(*) = Count(c.parent)) AS z)
FROM #patp AS a

DROP TABLE #patp
-PatP|||OK now, David...tell us why that works,now.

and show your work!!! ;)|||C'mon Pat. This is not the first time this guy has posted his homework for someone else to do. Reviewing his code for errors is one thing, but writing code for him is helping him cheat.|||let's hope he turns it in using the #patp table name, at least ;)

...and not even a "thank you, you can have half of my class credits"|||but writing code for him is helping him cheat.Only if he's incredibly gutsy and not very bright. I think he's smarter than that.

...and not even a "thank you, you can have half of my class credits"Why only half? If he retypes it for presentation, I'd give him 20%, but that's about all.

As TallCowboy0614 pointed out earlier, the real fun will come when David gets to explain that code for the class!

-PatP|||As TallCowboy0614 pointed out earlier, the real fun will come when David gets to explain that code for the class!

I'd be hard pressed....

working on it|||thanks for the code Pat!

I'm not a student, and that's not a homework assignment. (Been a while since then). I'm actually going to be using it (obviously re-worked to my own data) as an intergrity check for data Im pulling from a .xls file manually entered by a business user.

I was going to end up creating a function, but I knew someone here would have the guru-ishness to do it with a straight SELECT...obviously advantageous.

For simplicity I provided an example that I suppose looked like a homework assignment...LOL...the real scenario just would have been really annoying and difficult to explain. (and would have been one of those posts that people skip over)

as for explaining the code...cripes...i thought my SQL was pretty good. Lots more to learn. Although, I will be able to re-use what you provided Pat.

cheers.

Determining Trigger Order

I'm not sure if this is the correct forum or not for this question. Basically, i have 6 triggers attached to my table (2 of each type). I used sp_settriggerorder to make sure certian triggers fire first.

Is there any way to go back and determine what order a trigger has been set to (first, none or last)? I've scanned through all of the system tables and don't see anything.

Thanks in advance.

With some digging, you can find that they encode the order into the status fields on sysobjects. However, the way they are exposed is with the ObjectProperty() function -- it takes parameters such as ExecIsFirstUpdateTrigger.

It is not obvious to me how triggers are exposed from the Information_Schema views.

The digging involves using sp_helptext on the sp_settriggerorder and sp_helptrigger stored procedures.

|||That's exactly what I was looking for.

I didn't even think abou tusing sp_helptext to see what the procedure did. I'll definitely use that next time I want to know what SQL is doing.

Thanks!

Determining trigger execution state

Is there a way to determine if a trigger fired due to an insert or update
operation. I need to know this for the following reason. I have fields in
my tables that I want to initialize only when a record is inserted. If the
record is later updated, I do not want these fields changed. I also do not
want to have to maintain two seperate triggers to do this. In SqlAnywhere,
they have variables you check in the trigger to determine this as the
following example illustrates.
IF Inserting
!do inserting code
END IF
IF Updating
!do updating code
END IF
Is there something similar to this in SQL server?
Thanks in advance for any help.
Mark GenovyHi Mark
If any rows were affected, an insert trigger will only populate the inserted
table, but an update trigger will populate both inserted and deleted. So I
usually just look at the count(*) value from these tables. However, if no
rows were affected, the triggers will still fire, and both inserted and
deleted will have 0 rows. So then you can't tell what operation occurred.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Mark Genovy" <Mark Genovy@.discussions.microsoft.com> wrote in message
news:24381F86-0662-4913-A703-139BC0A9DF46@.microsoft.com...
> Is there a way to determine if a trigger fired due to an insert or update
> operation. I need to know this for the following reason. I have fields
> in
> my tables that I want to initialize only when a record is inserted. If
> the
> record is later updated, I do not want these fields changed. I also do
> not
> want to have to maintain two seperate triggers to do this. In
> SqlAnywhere,
> they have variables you check in the trigger to determine this as the
> following example illustrates.
> IF Inserting
> !do inserting code
> END IF
> IF Updating
> !do updating code
> END IF
> Is there something similar to this in SQL server?
> Thanks in advance for any help.
> Mark Genovy

determining the where statement

Hello,

Does anyone know how I can write the where statement according to the value?
In other words,
a command name would determine the where statement but the select statement is always the same.

This does not work:

create procedure new_procedure
(@.cid int, @.command VarChar(25))
Select * from a table
If @.command = 'pick1' begin
Where this = @.cid and that = 'Yes'
End
Else If @.command = 'pick2' begin
Where this = @.cid and that = 'No'
End
Else If @.command = 'pick3' begin
Where this = @.cid and that = 'No' and datefield >= DateAdd(d, -5, getdate())
End

something like that there.
Any ideas?
Thanks
Ehow many distinct values for @.command do you see ? is that something fixed like 3-4 options or would you have plenty of them..if you have only 3-4 options like you have above then you can do something like:

create procedure new_procedure
(@.cid int, @.command VarChar(25)) AS
begin
If @.command = 'pick1'
 select * from table Where this = @.cid and that = 'Yes'
Else If @.command = 'pick2'
 select * from table Where this = @.cid and that = 'No'
Else If @.command = 'pick3'
 select * from table Where this = @.cid and that = 'No' and datefield >= DateAdd(d, -5,
 getdate())
end

HTH