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 (https://blog.sqlauthority.com)

, , ,
Previous Post
SQL SERVER – 2005 – Version Information and Additional Information – Extended Stored Procedure xp_msver
Next Post
SQL SERVER – Effect of TRANSACTION on Local Variable – After ROLLBACK and After COMMIT

Related Posts

5 Comments. Leave new

  • Hello, I wonder if you know a way to protect my database (mydatabase.mdf) of being open in other computer..

    thanks

    Reply
  • Hi Fabian Maynez,

    You need to secure original file using local machine security.

    Regards,
    Pinal Dave ( https://blog.sqlauthority.com/ )

    Reply
  • Javier Rodriguez
    October 16, 2007 3:53 am

    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,

    Reply
  • Sérgio Santos
    April 15, 2008 1:50 pm

    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.

    Reply
  • Hardeep Singh
    July 9, 2008 2:07 am

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

    Reply

Leave a Reply

Menu