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