SQL SERVER – Understanding Restrict Access to Restricted_User Database Property

Recently I received an empty email with the subject “Question for you”. I was bit surprised as the email had no content at all. It was absolutely empty. I wrote back to user asking if he has missed the text in the email. He responded that there was an image already attached to the email and I needed to “enable display image in the email.” When I did the same, I found following image and the question from a user was inbuilt the image as well.

restricteduser q SQL SERVER   Understanding Restrict Access to Restricted User Database Property

The question was in database property what does Restrict Access = Restricted_User means? It is very clear to all of us what Multi_User is and what Single_user is. However, the real question was what Restricted_User stands for. As the question was sent in the form of image, I had decided to answer the same with the help of the image as well.

I quickly created following image and sent back in reply.

restricteduser SQL SERVER   Understanding Restrict Access to Restricted User Database Property

In database property what does Restrict Access = Restricted_User means that database can be accessed by users with sysadmin and dbcreator server roles and db_owner database role. After I sent the email, I really thought, it was a quite different type of communication and hence I am blogging the same over here.

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

5 thoughts on “SQL SERVER – Understanding Restrict Access to Restricted_User Database Property

  1. Good Evening Sir.
    A good day to you.
    I have a little doubt.
    I was creating DDL trigger. as per below.

    CREATE TRIGGER ColumnChanges
    ON dbo.Test
    AFTER Create_Table
    BEGIN –I am logging to database with sa.
    print ‘ Table created’
    — end of the code
    But when am writing a create table statement after execution, I am getting below error message.
    ” The specified event type(s) is/are not valid on the specified target object.” THe error number is1098.


  2. Hi,

    Can you please explain us what is the difference between a job and Maintenance plan? how to choose between a job or maintenance plan when a request can be done using both of them?


  3. Hi,

    I have few databases in restricted_user on an instance. Is it possible to know when these databases are kept in restricted_user mode. How?

    Thank you,


  4. HI,

    I have an issue in sqlserver 2008r2, where i need to create some thing in which User/Role should have NO access to read, write, edit or view access to the data but should be able to perform basic dba activities..How can I acheive this.
    I believe this can be done in 2012 by using user defined server roles.Correct me If I am wrong.
    I think back ups can be protected using TDE…
    Can you please assist


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