SQL SERVER – Stored Procedure to Know Database Access Permission to Current User

Jr. DBA in my company only have access to the database which they need to use. Often they try to access database and if they do not have permission they face error. Jr. DBAs always check which database they have access using following system stored procedure. It is very reliable and provides accurate information.

Sytanx:
EXEC sp_MShasdbaccess
GO

ResultSet: ( I have listed only one column)
AdventureWorks
AdventureWorksDW
master
model
msdb
MyDB
ReportServer
ReportServerTempDB
tempdb

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

5 thoughts on “SQL SERVER – Stored Procedure to Know Database Access Permission to Current User

  1. Hi,

    I have one question for you. I´m sure that you know the answer…jejej
    Is possible create Access Database from a Store Procedure in SQL Server 2005?

    Thanks in advance!
    Best Regards,

  2. Hi pinaldave,

    Well, i have a little question about store procedures…
    I want that with only one store procedure, im able to copy some elements of one database, to another… but i never worked with 2 diferent db so im ‘kinda stuck’… ^^

    Both databases are in the same SQL Server 2005…

    How can i update one database with the values of the other database?!

    Thank’z.
    Best Regards.

  3. Hi Sergio Santos,
    I hope I got your problem correctly. The reason is that its very simple. checkout the query below:

    update temp_table
    set amt = t.amt
    from
    (
    select amt from [DB2].[dbo].[OtherAmtTracking] where otheramttrackingid = 2
    ) as t
    where otheramttrackingid = 3

    I hope this helps you!! If this is not what you are asking for…then post your problm with some more details.

    Thanks!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s