Re: Regarding SQL Server Login

James Simpson (JamesSimpson@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
It would be for the entire server (the instance of the server) not just
the database for obvious reasons. How do I specify deny every
conceiveable type of group and leave only the sa to be able to login?

DROP LOGIN [BUILTIN\Administrators]

After this, SELECT * FROM sys.server_principals should return 18 rows.
The other 17 beside sa are fixed server roles, certificates and some
predefined logins/groups that I would not recommend that you drop, as
I have no idea of what could break if you do.

Then again, I am not sure that I see the point of permitting no one but
sa to access the server.

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

.

Relevant Pages

  • Re: Permission question - another one
    ... If I add an Sql Login it does add the TRAVAC\ in front of the names, ... seems to be users that were setup to use SQL Server Authentication. ... RAPTOR is the Server that has SQL Server running on it. ... > " I could think I am taking permissions away from someone, ...
    (microsoft.public.sqlserver.programming)
  • Re: Renamed Windows login not found in SQL Server 2000
    ... It's almost like SQL tuck some knowledge away in an area ... of memory that only gets released on Windows stop. ... > I am running SQL Server 2000 SP2 with Windows ... > login gets corrupted) I am unable to add the new login to ...
    (microsoft.public.sqlserver.security)
  • Re: Parameterized query problem
    ... Microsoft OLE DB Provider for SQL Server error '80004005' ... Cannot open database "Mydb" requested by the login. ... EXEC sp_helplogins 'aspuser' ...
    (microsoft.public.inetserver.asp.db)
  • Re: dsn-less connection
    ... It worked pretty much as I was hoping in prompting the user for a SQL ... Server login and was able to remove the specific dsn that's in the odbc ... straight to the SQL Server login prompt? ... with the login prompt but the Use Trusted Connection is checked on. ...
    (microsoft.public.access.security)
  • Re: Logging in irrespective of database access
    ... My problem is that in the Login section of Enterprise Manger I have to ... like there used to be in SQL 6.5. ... What's the point in having the Database Access section if the System Admin ... SQL Server MVP ...
    (microsoft.public.sqlserver.server)