Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
spierian
Starting Member
5 Posts |
 Posted - 2013-06-16 : 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
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-06-16 : 12:51:23
|
| Roles, users and users are what SQL server refers to collectively as principals. This page, in particular the link to Principals has good info, including how to create them etc. http://msdn.microsoft.com/en-us/library/bb510589.aspx |
 |
|
|
spierian
Starting Member
5 Posts |
Posted - 2013-06-18 : 10:29:28
|
| To James KThanks 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 specificationNo "login". Any suggestions? |
|
|
|
spierian
Starting Member
5 Posts |
Posted - 2013-06-18 : 11:01:50
|
| Sorry, should read "When I right-click the security folder AND POINT TO NEW I get:" |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-06-18 : 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 - 2013-06-18 : 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
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-06-18 : 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 - 2013-06-24 : 16:24:12
|
| James K, thank you very much. Your reply was very helpful. |
|
|
|
|
|
|
|
|
Explanation of roles, schemas etc