CODEWITHSUNDEEP
sql-serversql-server-2008
Yesudass Moses
Yesudass Moses

Reputation: 1859

Windows Administrator doesn't have permission on SQL Server

My previous system administrator created a SQL Server 2008 instance on my local machine with his domain User. Now, I cannot create, delete, or view any databases or tables on that instance. Even with my domain User, or Windows local administrator. It says "CREATE DATABASE permission denied in master". The users section in Management studio shows only two users 'BUILTIN\Users' and 'sa' which is disabled. 'Builtin\Users' has only public role. It seems no user have permission to this instance. How can my Local Administrator get permission to it ? I cannot delete the instance as it has important databases.

Upvotes: 3

Views: 13250

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46203

If your DBA didn't provision local Administrators as sysadmin role members. you can do that after installation if you are a local admin. From an administrator command prompt:

1) Start SQL Server in single-user mode

  • default instance: NET START MSSQLSERVER /m
  • named instance: NET START MSSQL$YourInstanceName /m

2) Run SQLCMD to add provision additional sysadmin role members:

  • default instance: SQLCMD -E -Q "sp_addsrvrolemember N'BUILTIN\Administrators', N'sysadmin';"
  • named instance: SQLCMD -E -S.\YourInstanceName -Q "sp_addsrvrolemember N'BUILTIN\Administrators', N'sysadmin';"

3) Restart SQL Server as normal and you can now connect as a sysadmin

Note you can specify any Windows user or group as sysadmin role members in step 2.

Use ALTER SERVER ROLE sysadmin ADD MEMBER [BUILTIN\Administrators]; instead of sp_addsrvrolemember in SQL Server 2012 and later versions. The sp_addsrvrolemember proc was deprecated in in SQL Server 2012 in favor of DCL syntax.

Upvotes: 5

Related Questions