Tech Journal Back to Tech Journal

Modifying user permissions in Microsoft SQL Server Express

You can run the following query to determine the database owner based on the master database information; the query assumes the database name is testdb.

select suser_sname(owner_sid) from sys.databases where name = 'testdb'

Now change context to the database and verify what is the owner according to the information stored in the database:

select suser_sname(sid) from sysusers where uid = user_id('dbo')

I expect that the owner would come out as something else than xxxx\ASPNET and it would be the same for both queries. Let me know if this is not the case.

Now verify whether the xxxx\ASPNET login is mapped to some user in the database; if it is, the following query should produce a row that lets you know the user name;

select * from sysusers where sid = suser_sid('xxxx\ASPNET')

If xxxx\ASPNET is not mapped to a user (the above query did not return a row), you can do the mapping by issuing:

CREATE USER [xxxx\ASPNET]

This should allow ASPNET to connect to the database. You can ignore the rest of the message if you issued the CREATE USER.

If the login was already mapped to a user, you should verify if the user had permission to connect to the database. You can issue the following statement to retrieve the permissions that the user has in the database:

select * from sys.database_permissions where grantee_principal_id = user_id('user_name')

There should be a row showing CONNECT for permission_name and GRANT for state_desc.

If there is no such row, you can grant the permission by issuing:

grant connect to <user_name>

This should allow ASPNET to connect.

[from http://social.msdn.microsoft.com/forums/en-US/sqlexpress/thread/b1fad2ef-305c-4e30-8b05-e126e659478b/ ]

Back to Tech Journal