| Author |
 Topic  |
|
|
spierian
Starting Member
5 Posts |
 Posted - 06/16/2013 : 08:45:23
|
I need to switch my website from Access to SQL Server 2008. I have managed to create the SQLS db, connect to it, and check/modify the scripts (vbscript) that access it. However I#m stuck on admin issues. The single dbo user I set up is not allowed to to use the SHRINK command in SQL Server Studio Express 2008. MY ISP tells me I need to set up another user with the appropriate permissions. I have no idea how to do that. Can anyone help?
Can anyone suggest a source that gives a simple explanation of users, database roles, schemas, permissions etc? |
|
|
James K
Flowing Fount of Yak Knowledge
3873 Posts |
|
|
spierian
Starting Member
5 Posts |
Posted - 06/18/2013 : 10:29:28
|
To James K
Thanks for taking the trouble to reply. I'd already found this document and (like some others) seems to consist mostly of terminology definitions.
In the section "To create a SQL Server login" it says "Right-click the Security folder, point to New, and select Login"
When I right-click the security folder I get:
User
Database role
Application role
Schema
Database audit specification
No "login".
Any suggestions? |
 |
|
|
spierian
Starting Member
5 Posts |
Posted - 06/18/2013 : 11:01:50
|
Sorry, should read
"When I right-click the security folder AND POINT TO NEW I get:" |
|
|
|
James K
Flowing Fount of Yak Knowledge
3873 Posts |
Posted - 06/18/2013 : 11:10:32
|
Within SSMS object explorer, there are levels of security. Directly under the server node, there is a security folder (which controls the server security) and under each database there is a security folder (which is specific to that database). You are right-clicking on the database level security. Instead find the server level security folder and right-click on that.
Having these two security folders is consistent with Microsoft's approach to SQL server security - take a look at the picture on this page http://msdn.microsoft.com/en-us/library/ms191465.aspx There are 3 levels of security - windows, server, and database. You create logins under the server level security. |
|
|
|
spierian
Starting Member
5 Posts |
Posted - 06/18/2013 : 12:25:24
|
| OK, thanks, I found that. But when I try to create a new log in, I get an error 15247 "user does not have permission to perform this action". I tried with default database set to "master" (which comes up by default) and then to my own database. Same result. In both cases I selected SQL Server authentication. |
|
|
|
James K
Flowing Fount of Yak Knowledge
3873 Posts |
Posted - 06/18/2013 : 12:30:23
|
quote:
Originally posted by spierian
OK, thanks, I found that. But when I try to create a new log in, I get an error 15247 "user does not have permission to perform this action". I tried with default database set to "master" (which comes up by default) and then to my own database. Same result. In both cases I selected SQL Server authentication.
That is probably because the login that you are using to access the server does not have enough privileges to create a new login. To create a login you need ALTER ANY LOGIN permission, or be a member of securityadmin group (or sysadmin).
When you look up the MSDN page for each command/utility, usually somewhere on that page they will also indicate what level of permissions are required to use that command/utility. For create login, this page has that info: http://msdn.microsoft.com/en-us/library/ms189751.aspx |
|
|
|
spierian
Starting Member
5 Posts |
Posted - 06/24/2013 : 16:24:12
|
| James K, thank you very much. Your reply was very helpful. |
|
|
| |
Topic |
|
Explanation of roles, schemas etc