How to determine if a user has a permission programmatically?

Hi,
I need to determine in advance if a user logged on to SQL Server 2005 can
execute certain privileged stored procedures and statements (such as
sp_addrolemember and ALTER LOGIN). To do this, I though I would need to
determine programmatically if a user has specific permissions (ALTER ANY
LOGIN, ALTER ANY CREDENTIAL, CONTROL SERVER, ALTER ANY USER, CREATE ROLE,
ALTER ANY ROLE).
Is there a way to perform this check programmatically via T-SQL? If this can
be done programmatically, is there a way to determine if a permission is
implied implicitly if it is not set up explicitly (e.g. via a group the user
belongs to, such as BUILTIN\Administrators)? Is there a better approach to
make the check?
Thanks,
AlekTry sp_helprotect , described here
http://msdn2.microsoft.com/en-us/library/aa933420(SQL.80).aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Alek" <alekDOTdavisATintelDOTcom> wrote in message
news:%23sEN40XHHHA.1064@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I need to determine in advance if a user logged on to SQL Server 2005 can
> execute certain privileged stored procedures and statements (such as
> sp_addrolemember and ALTER LOGIN). To do this, I though I would need to
> determine programmatically if a user has specific permissions (ALTER ANY
> LOGIN, ALTER ANY CREDENTIAL, CONTROL SERVER, ALTER ANY USER, CREATE ROLE,
> ALTER ANY ROLE).
> Is there a way to perform this check programmatically via T-SQL? If this
> can be done programmatically, is there a way to determine if a permission
> is implied implicitly if it is not set up explicitly (e.g. via a group the
> user belongs to, such as BUILTIN\Administrators)? Is there a better
> approach to make the check?
> Thanks,
> Alek
>|||In SQL Server 2005, you can use the has_perms_by_name
function to check the current users effective permissions on
securable.
http://msdn2.microsoft.com/en-us/library/ms189802.aspx
-Sue
On Mon, 11 Dec 2006 15:36:43 -0800, "Alek"
<alekDOTdavisATintelDOTcom> wrote:

>Hi,
>I need to determine in advance if a user logged on to SQL Server 2005 can
>execute certain privileged stored procedures and statements (such as
>sp_addrolemember and ALTER LOGIN). To do this, I though I would need to
>determine programmatically if a user has specific permissions (ALTER ANY
>LOGIN, ALTER ANY CREDENTIAL, CONTROL SERVER, ALTER ANY USER, CREATE ROLE,
>ALTER ANY ROLE).
>Is there a way to perform this check programmatically via T-SQL? If this ca
n
>be done programmatically, is there a way to determine if a permission is
>implied implicitly if it is not set up explicitly (e.g. via a group the use
r
>belongs to, such as BUILTIN\Administrators)? Is there a better approach to
>make the check?
>Thanks,
>Alek
>|||Thank you Remus. This is very helpful.
Alek
"Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon> w
rote
in message news:%233xgzrYHHHA.1248@.TK2MSFTNGP03.phx.gbl...
> Try sp_helprotect , described here
> http://msdn2.microsoft.com/en-us/library/aa933420(SQL.80).aspx
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "Alek" <alekDOTdavisATintelDOTcom> wrote in message
> news:%23sEN40XHHHA.1064@.TK2MSFTNGP04.phx.gbl...
>|||Thanks a lot Sue. This looks exactly like what I'm looking for. :-)
Alek
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:ng3sn2pdu6apq505ftk8mhnpp73l2i7mk7@.
4ax.com...
> In SQL Server 2005, you can use the has_perms_by_name
> function to check the current users effective permissions on
> securable.
> http://msdn2.microsoft.com/en-us/library/ms189802.aspx
> -Sue
> On Mon, 11 Dec 2006 15:36:43 -0800, "Alek"
> <alekDOTdavisATintelDOTcom> wrote:
>
>sql

How to Derive Parameters in Ad Hoc SELECT statements

Hi,

If I have ad hoc SQL statements created by users, which could be parameterized, how could I derive the parmeters at runtime. I cannot use CommandBuilder.DeriveParameters() as that is for StoredProcedures only.

Just use Split on the SQL string? Or is there a better way, such as a third-party .Net Component?

Thanks

John

We need more input on your problem. What does it mean that the users are creating the SQL Strings on their own, how does one look like ?

Jens K. Suessmeyer.

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

Hi,

The User's created SQL could be anything (they are writing a report!), but here is a trivial example

SELECT CustomerID, CustomerName FROM Customers WHERE CustomerID = @.CustomerID

Clearly, I have to Pop up a Window to the User for them to supply the actual run time value for @.CustomerID. Just like MS Access or the VS2005's Dataset Designer's Query Builder. Once I have the values I can populate Parameters Collection.

I was hoping someone would have some advise over Parsing SQL strings.

Thanks

John

|||

Best thing would be to regex the string and search for the matches within the string.

Jens K.

|||

Hi,

I've been looking at the General SQL Parser component, and it looks like I can simply get to the Field, Parameter pairs using this. Product is easily found, just do a Web search.

But, out of interests, Jens. Do you have contacts within the Microsoft Dev teams to find out how they do it in VS2005's Dataset Designer's Query Builder and Management Studio's Query Designer?

Rgds

John

How to delete data older than X days, without considering time

When running the following SQL statements, I get the same results.
Though I need to count only -30 days. Both statements below also
consider the time of the day as well, which is not desired

DELETE FROM MNT_R
WHERE MNT_R.TIMESTAMP < GETDATE()- 30

DELETE FROM MNT_R
WHERE MNT_R.TIMESTAMP < DATEADD(d, -30, GETDATE())

Here is the format of the values in column
MNT_R.TIMESTAMP
2005-08-09 06:06:44.577
2005-08-09 06:06:46.810
2005-08-09 06:06:49.060

So, since data are inserted into the MNT_R table every few seconds, my
delete statement will delete different number of rows, according to the
time of the day it runs.

Can you please post a SQL query that will not give me this headache?

thanx a lot allHi there,

You have to convert the source column to a non-using time format like
ISO:

DELETE FROM MNT_R
WHERE VARCHAR(10),MNT_R.TIMESTAMP < CONVERT(VARCHAR(10),GETDATE()-
30,112)

HTH, Jens Suessmeyer.|||nai (nioannides@.laiki.com) writes:
> When running the following SQL statements, I get the same results.
> Though I need to count only -30 days. Both statements below also
> consider the time of the day as well, which is not desired
>
> DELETE FROM MNT_R
> WHERE MNT_R.TIMESTAMP < GETDATE()- 30
> DELETE FROM MNT_R
> WHERE MNT_R.TIMESTAMP < DATEADD(d, -30, GETDATE())
>
> Here is the format of the values in column
> MNT_R.TIMESTAMP
> 2005-08-09 06:06:44.577
> 2005-08-09 06:06:46.810
> 2005-08-09 06:06:49.060
> So, since data are inserted into the MNT_R table every few seconds, my
> delete statement will delete different number of rows, according to the
> time of the day it runs.
> Can you please post a SQL query that will not give me this headache?

Instead of getdate() used convert(char(8), getdate(), 112) to strip
of the time portion.

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

How to defrag a table?

To all gurus,
Thanx for the info concerning the dbcc statements. The only remaining
question is there a command or code that is known to be used to defrag a
table. It would be very good if this would be usable on the fly (ad-hoc)
Thanx
BillO
*** Sent via Developersdex http://www.examnotes.net ***"Bill Orova" <nospam@.devdex.com> wrote in message
news:e3W#oPuZFHA.3876@.TK2MSFTNGP12.phx.gbl...
> To all gurus,
> Thanx for the info concerning the dbcc statements. The only remaining
> question is there a command or code that is known to be used to defrag a
> table. It would be very good if this would be usable on the fly (ad-hoc)
> Thanx
> BillO
>
> *** Sent via Developersdex http://www.examnotes.net ***
What do you mean by defrag a table?
A heap (table without a clustered index) stores data in no particular order.
As rows are deleted and then inserted SQL Server places those rows where
they fit.
If you have a clustered index on a table and the clustered index is
fragmented, then you could rebuild the clustered index and essentially
defrag your table.
Rick Sawtell
MCT, MCSD, MCDBA|||Hi,
See DBCC INDEXDEFRAG and DBCC DBREINDEX commands in sql server books online.
You could also see the command DBCC SHOWCONTIG in books online which details
the fragmentation for table.
Thanks
Hari
SQL Server MVP
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:uDT6fUuZFHA.3876@.TK2MSFTNGP12.phx.gbl...
> "Bill Orova" <nospam@.devdex.com> wrote in message
> news:e3W#oPuZFHA.3876@.TK2MSFTNGP12.phx.gbl...
> What do you mean by defrag a table?
> A heap (table without a clustered index) stores data in no particular
> order.
> As rows are deleted and then inserted SQL Server places those rows where
> they fit.
> If you have a clustered index on a table and the clustered index is
> fragmented, then you could rebuild the clustered index and essentially
> defrag your table.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
>|||
Rick,
So essentially if there is no clustered index then it would not be
possible to defrag a table(heap) or if possible it would have no
benevolent consequences in term of hard disk space?
BillO
*** Sent via Developersdex http://www.examnotes.net ***|||It depends on what you mean by fragmentation. For a heap, the is no order. B
ut as pages and extents
are allocated and rows and pages are deallocated, you can leave "holes" in y
our pages as well as
extents. I.e., pages not fully utilized and extents where not all 8 pages ar
e used. If you consider
this a type of fragmentation, then there is no way to defrag except re-loadi
ng the table or possibly
(shudder) shrinking the file/database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bill Orova" <nospam@.devdex.com> wrote in message news:O0EQbd6ZFHA.1088@.TK2MSFTNGP14.phx.gb
l...
>
> Rick,
> So essentially if there is no clustered index then it would not be
> possible to defrag a table(heap) or if possible it would have no
> benevolent consequences in term of hard disk space?
>
> BillO
> *** Sent via Developersdex http://www.examnotes.net ***